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()
 

MattP

Well-known member
Joined
Feb 29, 2008
Messages
1,206
Location
WY, USA
Programming Experience
5-10
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:

DekaFlash

Well-known member
Joined
Feb 14, 2006
Messages
117
Programming Experience
1-3
Using your code returns no data.

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



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.
 

Stonkie

Well-known member
Joined
Sep 12, 2007
Messages
279
Programming Experience
1-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.
 

DekaFlash

Well-known member
Joined
Feb 14, 2006
Messages
117
Programming Experience
1-3
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

 

Stonkie

Well-known member
Joined
Sep 12, 2007
Messages
279
Programming Experience
1-3
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...
 

DekaFlash

Well-known member
Joined
Feb 14, 2006
Messages
117
Programming Experience
1-3
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
 

Stonkie

Well-known member
Joined
Sep 12, 2007
Messages
279
Programming Experience
1-3
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
 

DekaFlash

Well-known member
Joined
Feb 14, 2006
Messages
117
Programming Experience
1-3
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.



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
 

Stonkie

Well-known member
Joined
Sep 12, 2007
Messages
279
Programming Experience
1-3
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.
 

DekaFlash

Well-known member
Joined
Feb 14, 2006
Messages
117
Programming Experience
1-3
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.
 

DekaFlash

Well-known member
Joined
Feb 14, 2006
Messages
117
Programming Experience
1-3
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
 

Stonkie

Well-known member
Joined
Sep 12, 2007
Messages
279
Programming Experience
1-3
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:

DekaFlash

Well-known member
Joined
Feb 14, 2006
Messages
117
Programming Experience
1-3
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.
 

Stonkie

Well-known member
Joined
Sep 12, 2007
Messages
279
Programming Experience
1-3
I'm not sure I understand what you mean... At the moment, it uses the strongest "B" and if there are equals, it sorts by lowest "FDB". There has to be one criteria more important than the other unless you have an equation

VB.NET:
x * B - y * FDB

where x/y is a constant relative importance of B over FDB.

It would give something like this if they are of same importance :

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

DekaFlash

Well-known member
Joined
Feb 14, 2006
Messages
117
Programming Experience
1-3
I am looking at criteria which will help me identify the two examples as illustrated in the screen capture below.



There are two types of selection criteria but both requre highest B and lowest fdb values.
 

Stonkie

Well-known member
Joined
Sep 12, 2007
Messages
279
Programming Experience
1-3
Well, I must admit I still don't understand what you mean by highest B and lowest fdb values. In your example, the highest B happens to be attributed to the row with the lowest FDB. What should happen when you have these values :

B - FDB
2 - 2
3 - 3
4 - 4
5 - 5

If you can come up with an explanation like "B and FDB are inversely correlated and we can just take the highest B" or some other explanation that relates both criteria to say which one is most important in which case, then I can translate that to SQL, but for now I just don't understand what is supposed to happen.
 

DekaFlash

Well-known member
Joined
Feb 14, 2006
Messages
117
Programming Experience
1-3
The screen captures I provided are from a daily racing analysis we provided, we have two sets of criteria used to identify different types of bets.

We only bet if the criteria highlights selections on the first or second row of each group of 3.

One of the common factors for all selections is that they must have the highest b and lowest fdb otherwise they do not qualify as a selection.
 
Top Bottom