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()
 
Bolds and Arrows is another part of the criteria - each selection must have at least 1 bold and 1 arrow hence the > 1 but as this is easier to implement I've only just mentioned it.

I'll look at adding race = @race as you suggested, at the moment however from the main screen they select if they want to look at data from a specific date or specific race and this then sets up parameters.

When the analysis window is opened, these parameters help load the correct dataset from the main form into the datagrid on the analysis form.

I'm new to sql so may require help on how to join, if once i get this working i
notice a performance issue we can see if it can be boosted.

Using your code 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 still returns a blank grid.
 
Last edited:
When you add filtering for the event, it will return results. Right now you are considering the whole lot of race results as a single race with thousands of contestant and none of them have both the best B and FDB to be returned. You must limit this to a certain event for the result to make sense.
 
Deka

To add a bit of generic info to this discussion and hopefully aid your understanding of how databases work and how they complete queries, I'd like you to consider the following:


When working with data in databases we are always thinking in terms of a set of data. A SELECT * FROM x will produce a set of all X. SELECT * FROM x WHERE ... will produce a subset of x. When you go on to add grouping the database will reduce the set to a subset, and then start any counting or summing you have told it to do in the group. Any columns that are not counted, summed or have some other aggregate function performed on them must be declared to be group members, and each unique value they contain contributes to the grouping.

Because everything in a database realises a set, a set output from one part of a query can be used as input to another part of the query. SELECT * FROM (SELECT * FROM x WHERE ...) WHERE ...
is a perfectly valid query and the database will first create a subset and output the results, then take that subset and apply another filter, further reducing the size, then output the results again [actually, for the purists, it's sometimes a fib; the database will rewrite your query and in this case it would be perfectly valid to push the two where clauses together because they are effectively an AND.. but we'll leave that truth alone for now].

To effectively have a database answer your problem you must boil it down into sets and subsets, and then understand how sets of data come together.
JOIN connects sets of data horizontally. Some columns must be equal to allow the database to assert that the rows should be linked.
A JOIN B ->
AB

UNION connects sets of data vertically, and the number of columns must be equal
A UNION B ->
A
B


When you have GROUP prepare a set with aggregated results, the detail is irretrievably lost. There are ways of having the database perform aggregate operations alongside the main operation and putting the values into the detailed output, but these are analytical queries and I'll leave them alone. A WHERE clause is applied before a GROUP, and a HAVING clause is applied after.
SELECT a, count(b) as c_b FROM x WHERE y > 100 GROUP BY a HAVING c_b > 20
The above query finds everything in x having a y greater than 100, then creates a group of A and how many times each A value occurs, but only returns values of A where they occur more than 20 times (in the subset)


Have a think of how to state your problem in terms of sets..

In your problem
 
Back
Top