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()
 
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
 
I am looking at criteria which will help me identify the two examples as illustrated in the screen capture below.

Criteria.jpg


There are two types of selection criteria but both requre highest B and lowest fdb values.
 
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.
 
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.
 
And how would you order these values?

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

Or would you reject them all because none of the rows has the best in both column at the same time? And do we still need the 3 best? Or is 3 only a maximum and we can have no rows at all that actually have both the highest B and lowest FDB at the same time?
 
Actually its B the highest and FDB the lowest, so the first example (5-2) would indicate a selection and the others would be disregarded.

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

The majority of the time we will always produce the 3 best, the only exception is on the rare occasion where there are only 2 horses in that race.

There are instances where you can have joint b or joint fdb values or instances where one of the conditions is met but not the other.

There are other conditions to be met in regards to the selection criteria but identifying B and FDB is the first condition we need to look for.

I think to give you a better idea, I'll upload a copy of the analysis file.

Analysis File - 01/11/08

I'll highlight rows matching selection criteria 1 in yellow and those matching selection criteria 2 in blue.
 
Alright... So, doesn't the last query do that?
 
Alright... So, doesn't the last query do that?

I believe the last query example you provided me with was

SELECT TOP(3) * FROM VsrData ORDER BY (B - FDB) DESC

I modified your code as follows - Dim sql As String = "SELECT * FROM VsrData where bolds > 1 and arrows > 1 ORDER BY (b-fdb) desc, Jockey DESC, TRAINER desc"

When I run that as a query it highlights, SHE as a selection even though it doesn't have the highest B.

Selection.jpg
 
From that last example, let's compare LIT and MAM. LIT has the highest B and MAM has the lowest FDB. Which one should come first?
 
How can you get stable results then? I mean, if the highest B does not have the lowest FDB, no other record can have the highest B and no record can be a valid selection. And since B and FDB are not strictly inversely correlated, you will end up with no selection a good part of the time. And I mean that from a mathematical point of view, not from the code. It is not possible to get any selection from the table you showed in the last post.

Is that the expected behavior? If so, you can try this :

VB.NET:
SELECT * FROM VrsData
WHERE (B IN (SELECT MAX(B)  FROM VrsData)) AND 
(FDB IN (SELECT MIN(FDB) FROM VrsData))

Now, how many records does your VrsData table contain? Does it only contain the data for the currently processing race? Otherwise you will have to add filters to that race but I don't know which column your foreign key is.

By the way, this will give no result most of the time. It's not an anomaly, it simply doesn't return any record that does not fit both having the highest B and the lowest FDB at the same time and there isn't necessarily one all the time. This will not return more than one result either unless you have ties for both the B and FDB.
 
Yes that is the expected behaviour, as a result the number of times the selection criteria is met for the daily analysis can vary and on some occasions there may not even be any selections.

The reason I highlighted that table was because your previous code identified a wrong selection.

At the moment the VsrData table contains 6597 records but this is only for March 08 as i'm still in the process of importing data over from the old system.

I have put in filters based on date, time and venue.

I'll test your code and let you know the outcome thanks.
 
Both SELECT * FROM VsrData WHERE (B IN (SELECT MAX(B) FROM VsrData)) AND (FDB IN (SELECT MIN(FDB) FROM VsrData)) and bolds > 1 and arrows > 1 and SELECT * FROM VsrData WHERE (B IN (SELECT MAX(B) FROM VsrData)) AND (FDB IN (SELECT MIN(FDB) FROM VsrData)) and bolds > 1 and arrows > 1 ORDER BY Jockey DESC, TRAINER desc return no selections.
 
Of course, we're comparing the values for the event to the minimum and maximum throughout the whole table. We must set the same filter to both inner selects. Here :

VB.NET:
SELECT * FROM VsrData WHERE (B IN (SELECT MAX(B) FROM VsrData WHERE bolds > 1 and arrows > 1)) AND (FDB IN (SELECT MIN(FDB) FROM VsrData WHERE bolds > 1 and arrows > 1)) and bolds > 1 and arrows > 1

Now I don't really understand what "bolds" and "arrows" mean and why you use "> 1". Do you use the database to determine what is selected in the user interface? If you don't specify "race = @race", this will take the matching selection for all of the records in the table and there are good chances it won't return anything.

By the way, this is not the most efficient way of doing it. It should be better to join the table on self, but I always come up with the inner select solutions first. It seems to make more sense to me. If performance is a problem, I can probably figure out at way to boost that up a bit.
 
Back
Top