Question Sql Select from group by

DekaFlash

Well-known member
Joined
Feb 14, 2006
Messages
117
Programming Experience
1-3
I have an application into which data on horse races is entered, I then click calculate to work out and save the top 3 horses into a seperate database.

I am trying to find the highest value within any column in consecutive groups of 3 rows in a datagrid.

The 3 rows are grouped using a flag in the last column on the 3rd row.

However the following code results in incorrect syntax near = exception.

I hope you can assist, thanks.

VB.NET:
Dim sql As String = "SELECT max(Jockey) FROM VsrData group by EndOfRace = Y "
        Try
            
            racetypeda = New SqlDataAdapter(sql, conn)

            racetypeda.SelectCommand = New SqlCommand(sql, conn)
            
            builder = New SqlCommandBuilder(racetypeda)

            conn.Open()

            racetypeds.Clear()

            racetypeda.Fill(racetypeds, "VsrData")

            VsrDataDataGridView.DataSource = racetypeds.Tables("VsrData")

        Catch ex As Exception
            MsgBox(ex.ToString.Trim)
        End Try
        conn.Close()
 
From what I can guess you're looking for. If this isn't what you want give a couple of mock lines from the database and the desired results and I'll refine it.

VB.NET:
Dim sql As String = "SELECT MAX(Jockey) FROM VrsData WHERE EndOfRace = 'Y'"
 
Last edited:
Using your code returns no data.

Here is an example of the data I am working with.

TopThreeRated.jpg


Say for example I'm after finding the highest value within any of the columns after and including the column labelled A.

This needs to be applicable to each group of 3.
 
As you can see here : SQL GROUP BY and HAVING GROUP BY does not take an equal sign, but you can use the column of the GROUP BY inside the query.

Maybe this would do it : SELECT MAX(Jockey) FROM VrsData WHERE EndOfRace = 'Y' GROUP BY EndOfRace

I don't have a database to try this at the moment, but I'm pretty sure this will work.
 
Using the following code, results in a empty datagrid.

VB.NET:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        
        Dim maxjockey As Integer
        maxjockey = CInt(ds.Tables("VsrData").Compute("MAX(Jockey)", String.Empty))

        Dim maxtrainer As Integer
        maxtrainer = CInt(ds.Tables("VsrData").Compute("MAX(Trainer)", String.Empty))

        Dim maxbolds As Integer
        maxbolds = CInt(ds.Tables("VsrData").Compute("MAX(Bolds)", String.Empty))

        Dim maxarrows As Integer
        maxarrows = CInt(ds.Tables("VsrData").Compute("MAX(Arrows)", String.Empty))

        MsgBox("Jockey " + maxjockey.ToString)
        MsgBox("Trainer " + maxtrainer.ToString)
        MsgBox("Bolds " + maxbolds.ToString)
        MsgBox("Arrows " + maxarrows.ToString)

        Dim sql As String = "SELECT MAX(Jockey) FROM VsrData WHERE EndOfRace = 'Y' GROUP BY EndOfRace"
        Try
            
            racetypeda = New SqlDataAdapter(sql, conn)

            racetypeda.SelectCommand = New SqlCommand(sql, conn)
            
            builder = New SqlCommandBuilder(racetypeda)

            conn.Open()

            racetypeds.Clear()

            racetypeda.Fill(racetypeds, "VsrData")

            VsrDataDataGridView.DataSource = racetypeds.Tables("VsrData")
            MsgBox(VsrDataDataGridView.Rows.Count.ToString)
        Catch ex As Exception
            MsgBox(ex.ToString.Trim)
        End Try
        conn.Close()

    End Sub

blankgrid.jpg
 
I don't know why I thought about this... The GROUP BY had totally no effect! Must have been one of those late shifts! Sorry for having you waste your time on useless tips... :eek:

Yet, any query that has a single column in the select clause can never return more than one column so it could hardly fill your data table...

Maybe this is what you are looking for or something close to it :

VB.NET:
SELECT TOP 3 * FROM VrsData WHERE EndOfRace = 'Y' ORDER BY Jockey DESC

I'm really not certain I understand your question though... This will give you the 3 lines in VrsData that have the highest Jockey values and have EndOfRace to "Y".

If this is not it, try to give more info about what the query is supposed to do...
 
Whilst your code has its uses, there are times I have to work with subsets of the data.

How would I get it to do a similar thing with a subset of data (this is data that is filtered).

Thanks
 
If you need to limit the data returned, you can use a combination or the TOP keyword and the ORDER BY statement to limit the number of rows to get a certain number of records that meet a condition best (the 20 latest posts). Otherwise, you would use the WHERE clause to limit the records to those that meet a condition exactly no matter how many records are returned (all posts since yesterday).

If you need to limit the columns, you can replace "*" with the list of columns you need to get data for like this :

VB.NET:
SELECT TOP 3 EndOfRace, Jockey FROM VrsData WHERE EndOfRace = 'Y' ORDER BY Jockey DESC
 
Thanks, the last code example you provided still returns a blank grid.

I will try and provide a clearer explanation of what I am trying to achieve.

I have an application into which data on horse races is entered, I then click calculate to work out and save the top 3 horses into a seperate database.

I am trying to find the highest value within any column in consecutive groups of 3 rows in a datagrid.

The 3 rows are grouped using a flag in the last column on the 3rd row.

I am looking at running various queries on the data within the screen capture.

DataExample.jpg


1) Identifying highest value in the column labelled 1 for each group of 3.
2) Identifying highest value in the column labelled 2 for each group of 3.
3) Identifying data within columns labelled Bld and >> in a similar style to the queries mentioned in 1) and 2).

I also have to be able to perform these queries on results of data returned from a previous query.

4) Once I deduct the value in the column labelled 4 from the value in the column labelled 5, I have to identify when the highest score within the 3 results falls on the same line as highest B and Lowest FDB.

Using VB.net and Sql is relatively new to me, so your assistance is greatly appreciated.

Thanks
 
Alright, so each group of 3 is supposed to represent a tournament or a pairing table?

Is there any column with a pairing table id that uniquely identifies that group of 3? Are groups defined by the order they are entered in the application (it is a bad idea to use the id for other purpose than identifier and linking to other tables)? If so, we can work something out by using a group by on that column or on a calculation from the Id (something like "Id - (Id % 3)"). Ask how if this is your situation and you need help :) .

Or maybe it would be simpler off finding the right columns from your application and retrieving the matching rows from the database one by one.
 
Each group of 3 is the result of a calculation to identify the strongest 3 horses from any race.

There is a column which identifies which of the 3 we are dealing with - this is non unique and there is also a column containing a unique id, this is generated in code to prevent duplicate data entry - however using this column may be awkward.

I'm not sure if your group by or calculation from id applies to my situation but it sounds like it may be worth discussing this further, as suggested in your previous post.
 
Alright, so each group of 3 is supposed to represent a tournament or a pairing table?

Is there any column with a pairing table id that uniquely identifies that group of 3? Are groups defined by the order they are entered in the application (it is a bad idea to use the id for other purpose than identifier and linking to other tables)? If so, we can work something out by using a group by on that column or on a calculation from the Id (something like "Id - (Id % 3)"). Ask how if this is your situation and you need help :) .

Or maybe it would be simpler off finding the right columns from your application and retrieving the matching rows from the database one by one.

Hi Stonkie - I was wondering if you had a chance to read my last post.

Thanks
 
Sorry, I got the email saying you'd answered, but it was late, I didn't have the time to read and I forgot to look back at the post!

Maybe you can try this

VB.NET:
SELECT TOP(3) * FROM VrsData ORDER BY B DESC, FDB ASC

That should give you the top 3 rows with the highest "B" values (and if two are equal, it gets the lowest "FDB" ones) among the whole table. You may use this to get those for a certain race (I used the column name "Race" because I do not know which column tells which race the data applies to) :

VB.NET:
SELECT TOP(3) * FROM VrsData WHERE Race = 1 ORDER BY B DESC, FDB ASC

Now, if you want to get a list of all the groups of three best per race, we have to use a different approach and I'm not too sure about the procedure. Here's some code that might not work, I'll try it later once I've installed SQL Server at home (formatted the computer recently). I suppose you have a unique identifier called "Id" :

VB.NET:
SELECT * FROM VrsData AS OuterData WHERE Id IN (SELECT TOP(3) Id FROM VrsData WHERE Race = OuterData.Race ORDER BY B DESC, FDB ASC)

This last one I am really not sure it works or even runs... I'll get back to you to say if it works when I can finally try it, probably tomorrow.

EDIT : It actually does work! Except for a little detail, it gives the worst instead of the best ones, I just corrected it. Tell me if none of those give what you are looking for.
 
Last edited:
Maybe you can try this

VB.NET:
SELECT TOP(3) * FROM VrsData ORDER BY B DESC, FDB ASC

That should give you the top 3 rows with the highest "B" values (and if two are equal, it gets the lowest "FDB" ones) among the whole table. You may use this to get those for a certain race (I used the column name "Race" because I do not know which column tells which race the data applies to) :

Is it possible to AND the b desc and fdb asc statements, so it gives me highest b and lowest fdb.
 
Back
Top