-
Dec 11th, 2013, 07:53 PM
#1
Thread Starter
Addicted Member
[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
-
Dec 11th, 2013, 07:59 PM
#2
Re: DataSet SQL GROUP By statement not working
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
That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma
Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney
-
Dec 11th, 2013, 08:09 PM
#3
Thread Starter
Addicted Member
Re: DataSet SQL GROUP By statement not working
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.
-
Dec 11th, 2013, 08:39 PM
#4
Re: DataSet SQL GROUP By statement not working
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.
-
Dec 11th, 2013, 08:43 PM
#5
Thread Starter
Addicted Member
Re: DataSet SQL GROUP By statement not working
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.
-
Dec 11th, 2013, 08:55 PM
#6
Re: DataSet SQL GROUP By statement not working
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.
-
Dec 11th, 2013, 09:09 PM
#7
Thread Starter
Addicted Member
Re: DataSet SQL GROUP By statement not working
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.
-
Dec 11th, 2013, 09:47 PM
#8
Re: DataSet SQL GROUP By statement not working
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.
-
Dec 12th, 2013, 04:09 AM
#9
Re: DataSet SQL GROUP By statement not working
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)
-
Dec 13th, 2013, 04:44 AM
#10
Re: DataSet SQL GROUP By statement not working
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.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Dec 15th, 2013, 04:26 PM
#11
Thread Starter
Addicted Member
Re: DataSet SQL GROUP By statement not working
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.
Last edited by Fedaykin; Dec 15th, 2013 at 04:30 PM.
-
Dec 16th, 2013, 04:09 AM
#12
Re: [RESOLVED] DataSet SQL GROUP By statement not working
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.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|