[RESOLVED] DataSet SQL GROUP By statement not working

I'm querying an SQL view into a DataSet inside VB.net.

I've been trying to do a simple Group By in my DataSet query that returns the error:

Column "'KitWIP.DUE_DATE is invalid in the selected list because it is not contained in either an aggregate function or the GROUP BY clause."

Everything I've read about the SQL GROUP By says I can just group by one column after an aggregate function. What am I missing?

Code:

---


SELECT        DUE_DATE, START_DATE, CUSTNO, SONO, COMPLETE, BLDQTY, ORD_TYPE, BALANCE, OPENCLOS, DESCRIPT, Expr1, PART_NO, WONO, REVISION, FabDue,
                        PartsDue, QuoteNo, MAX(PK_ProjSetup) AS LatestPK
FROM            KitWIP
GROUP BY WONO

---

Like it says, your columns must be in the group clause, or in an aggregate function to be selected. See these examples

Code:

---

--additional column in group clause
SELECT        WONO, QuoteNo, MAX(PK_ProjSetup) AS LatestPK
FROM            KitWIP
GROUP BY WONO, QuoteNo

--additional column in aggregate function
SELECT        WONO, Min(QuoteNo) as FirstQuote, MAX(PK_ProjSetup) AS LatestPK
FROM            KitWIP
GROUP BY WONO

---

Thank you for the examples, but the end result that I'm after is to just Group By WONO, I don't want to group by any other column and I have no function to perform on any other column.

Quote:

---

Originally Posted by Fedaykin

Thank you for the examples, but the end result that I'm after is to just Group By WONO, I don't want to group by any other column and I have no function to perform on any other column.

---

You can't. How would that make sense? How are you going to group together multiple records with the same WONO value if they have values in other columns that are different? The only way you could do that is if WONO was the only column you were projecting. Quite simply, every column in your SELECT clause that is not involved in an aggregate must be specified in the GROUP BY clause.

I understand, however there is only one column that is different. It's the pk_ProjSetup column. I only want to return the row that has the MAX pk_ProjSetup value for any WONO.

Quote:

---

Originally Posted by Fedaykin

I understand, however there is only one column that is different. It's the pk_ProjSetup column. I only want to return the row that has the MAX pk_ProjSetup value for any WONO.

---

Then you're going to have to perform two queries. The first will get the MAX(PK_ProjSetup) grouped by WONO and then the second will get all columns where PK_ProjSetup and WONO have the values from the first result set.

By the way, this thread has nothing to do with VB.NET. It's purely a SQL question and therefore belongs in the Database Development forum. I will ask the mods to move this one and please post database questions in that forum in future.

That sounds correct, because moving the all of the column names to the Group By statement returned results, but did not eliminate the duplicates:

Code:

---


SELECT        DUE_DATE, START_DATE, CUSTNO, SONO, COMPLETE, BLDQTY, ORD_TYPE, BALANCE, OPENCLOS, DESCRIPT, Expr1, PART_NO, WONO, REVISION, FabDue,
                        PartsDue, QuoteNo, MAX(PK_ProjSetup) AS LatestPK
FROM            KitWIP
GROUP BY DUE_DATE, START_DATE, CUSTNO, SONO, COMPLETE, BLDQTY, ORD_TYPE, BALANCE, OPENCLOS, DESCRIPT, Expr1, PART_NO, WONO, REVISION, FabDue,
                        PartsDue, QuoteNo

---

As far as the location of this post, I disagree. I'm working inside Visual Studio, inside of a DataSet, programming in VB.net. SQL statements are common place when programming in any language supported by Visual Studio. Please leave the question where I posted it.

Quote:

---

Originally Posted by Fedaykin

As far as the location of this post, I disagree. I'm working inside Visual Studio, inside of a DataSet, programming in VB.net. SQL statements are common place when programming in any language supported by Visual Studio. Please leave the question where I posted it.

---

No, it belongs in Database Development. SQL is SQL and the fact that you're working in VS with VB.NET is irrelevant. You could be working in C# or PHP or SQL Server Management Studio and the SQL code would be exactly the same regardless. That's like asking a question about knitting on an automotive forum because you happen to be in a car while your doing your knitting. Almost everyone who posts in the Database Development forum is writing an application in a .NET language or VB6 but their questions are about SQL, which is specific to databases so they ask them in the forum dedicated to databases.

Thread moved to the 'Database Development' forum - which is where you should always post SQL questions (while SQL can be used in VB.Net, it is certainly not specific to VB.Net)

Fedaykin, JM is doing you a favout by asking for it to be moved (and Si is doing you one by moving it). You're going to get much better answers to your question in the DB forum than you are in the VB.Net one.

The reason all columns in the select list must be part of an aggregate function or be included in the group by is because otherwise SQLServer would not be able to resolve a single value for them when more than one possible value was presented. Take the following set of Fruit data:-

Code:

---

FruitID FruitType  Colour
1        Apple      Green
2        Apple      Yellow
3        Apple      Red
4        Apple      Green
5        Banana    Yellow

---

If you were to write the following query:-

Code:

---

Select FruitType, Colour
From Fruit
Group by FruitType

---

What would the result be? For a banana the result is obvious, it's banana, yellow because that's the only possible answer from that data set. But what about apples? What value should be returned as the colour? You only want one row for Apples (because you specified that you wanted to group by Fruit Type) and you haven't given the database a mechanism for deciding which colour to return. For the query to make sense you would either have to group by both Fruit type and Colour (in which case you would get all three colours returned for Apples each returned as a separate row) or you would have to put colour in an aggregate function, e.g. Reddest (in which case you would get Red, because that's the reddest).

When you've got a load of rows where all the value are identical as you described the correct thing to do is either put them in an arbitrary aggregate function like Min or include them in the group by. An arbitrary aggregate fuction works because it will return the same value for all rows if they're all the same (e.g. the reddest of red, red and red is... well... red) so it can be resolved to a single value. Putting them all in the group by is probably better though because it's explicitely collapsing the rows.

As for why your query isn't eliminating the duplicates, it is. That's what Group By does. Which means what you're seeing aren't duplicates. I suggest you look very closely at two rows that appear to be duplicates because somewhere in tere there is a deffierence.

Thank you for all the feedback. You are correct that there are several differences causing the repeated lines. This was always known, I just mistakenly thought I could filter these with a single database view or SELECT statement. I ended up using a DataTable and DataSet and a For Each Row to get the granularity needed. This may not be the most efficient method, but returns the single rows needed. You could do all this with a single DB open/close, I just separated for clarity.

Code:

---


    Dim dtWO As New DataTable
    Dim dsFill As New DataSet

        '''''''''''''''''''''''''''
        'Fill a DataTable
        Dim sqConW As New SqlClient.SqlConnection("Server=MYSERVER\SQLSTANDARD;Database=MYDATABASE; Trusted_Connection=True;")
        Dim sqCmdW As New SqlClient.SqlCommand
        Dim SqlW As String

        sqCmdW.Connection = sqConW            'create the DB connection
        SqlW = "SELECT DISTINCT WONO FROM KitWIP"  'This produces a list of one each Work Order numbers
        sqConW.Open()                        'open the connection

        Dim AdapterW As New SqlDataAdapter

        AdapterW.SelectCommand = New SqlCommand(SqlW, sqConW)
        AdapterW.Fill(dtWO) 'Fills the Dataset with the list of single Work Order numbers
        sqConW.Close() 'close the connection
        '''''''''''''''''''''''''''
        '''''''''''''''''''''''''''

        'Fill a Dataset
        Dim dbWONO As String
        Dim AdapterF As New SqlDataAdapter

        For Each Row As DataRow In dtWO.Rows
            dbWONO = Row.Item("WONO")

            'Fill a Dataset
            Dim sqConF As New SqlClient.SqlConnection("Server=MYSERVER\SQLSTANDARD;Database=MYDATABASE; Trusted_Connection=True;")
            Dim sqCmdF As New SqlClient.SqlCommand
            Dim SqlF As String

            sqCmdF.Connection = sqConF            'create the DB connection
            SqlF = "SELECT TOP (1) PART_NO, REVISION As Rev, CUSTNO, WONO, DESCRIPT, BLDQTY, DUE_DATE, ORD_TYPE, SONO, FabDue, PartsDue, QuoteNo, QuotePartNo FROM KitWIP WHERE WONO = '" & dbWONO & "' ORDER BY PK_ProjSetup DESC"

            'The SELECT statement above picks the latest primary key from the database by sorting descending and then choosing the top 1 primary key.
           
            sqConF.Open()                        'open the connection
            AdapterF.SelectCommand = New SqlCommand(SqlF, sqConF)
            AdapterF.Fill(dsFill) 'Fills a Dataset with the sql command above
            sqConF.Close() 'close the connection
        Next

        '''''''''''''''''''''''''''

        'Populate the DataGridView with the dataset filled by the Adapter above
        Me.DataGridView1.DataSource = dsFill.Tables(0)

---

Normally I feel a great sense of victory when achieving my goal.. this time I'm still just bitter about it. My amateur opinion is that SQL should have some more robust filtering capabilities. Hopefully this will assist someone else.

Quote:

---

My amateur opinion is that SQL should have some more robust filtering capabilities.

---

It does. The problem is that you still haven't fully understood the inter-relationship between the Group By statement, the select statement and aggregate functions. When collapsing rows you can't just expect sql server to throw conflicting values away, you have to tell it what to do with them. This is very deliberate, you don't want sql server making arbitrary decisions for you because that could result in unexpecetd (and often undesirable) outcomes. Don't be so quick to condemnn the tools, that's the sign of a bad workman.

Here's how you could have achieved the same result with a single query:-

Code:

---

with cteTopPKs as
(Select WoNo, Max(PK_ProjSetup) as PK
From KitWIP
Group by WoNo)
SELECT PART_NO, REVISION As Rev, CUSTNO, KW.WONO, DESCRIPT, BLDQTY, DUE_DATE, ORD_TYPE, SONO, FabDue, PartsDue, QuoteNo, QuotePartNo
FROM KitWIP KW
Join cteTopPKs TPK
  on KW.PK_ProjSetup = TPK.PK
Order By PK_ProjSetup Desc

---

I typed that straight in to the post so haven't syntax checked it. You might find an error or two's crept in so be a bit careful with it.

The principle is that the common table expression returns all the Top (or Max) Primary keys. You then join to that result in your main select statement and it acts as a filter. The final Order By is probably redunandant. I included it so that you'd get the same same ordering as in your code but I get the impression your more interested in the filtering than the ordering.