We help IT Professionals succeed at work.

Group By Giving me the Wrong Data

Aravind Ranganathan
on
I have a sql query that groups by upcitem number and must return back the record with the highest value but in my example it is returning the lowest value back.

SELECT     [Unit UPC Base Item], [Item Description]
FROM         (SELECT     MIN([Item Description]) AS [Item Description], [Unit UPC Base Item]
                       FROM          dbo.ItemDescLookups
                       GROUP BY [Unit UPC Base Item]) AS Sub

Open in new window


Table result i want the value with  the 10 returned.
Comment
Watch Question

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Pls try this -

use MAX. -  You need to use the MAX function to get the Maximum value from a COLUMN


SELECT     [Unit UPC Base Item], [Item Description]
FROM         (SELECT     MAX([Item Description]) AS [Item Description], [Unit UPC Base Item]
                       FROM          dbo.ItemDescLookups
                       GROUP BY [Unit UPC Base Item]) AS Sub

Open in new window


Another option is you can use a ROW_NUMBER function.
Daryl BamforthTechnical Expert

Commented:
Group by doesn't change the order.

Add

order by [Unit UPC Base Item]  desc

Open in new window

Aravind RanganathanWindows Application Developer

Author

Commented:
@Pawan Kumar that is giving me the 6th record which has a countofdescription = 1
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
From where you are getting CountofDescription column.
Daryl BamforthTechnical Expert

Commented:
So your wanting it to return the one with 10?
Aravind RanganathanWindows Application Developer

Author

Commented:
@ Pawan the count of description is part of the table, i am not running any Count to get that.
Aravind RanganathanWindows Application Developer

Author

Commented:
@ Daryl Banfort yes i want it to return the one with 10
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Pls try this

SELECT MIN([Item Description]) AS [Item Description], [Unit UPC Base Item]
	 , MAX(CountofDescription) CountofDescription 
FROM  dbo.ItemDescLookups
GROUP BY [Unit UPC Base Item] 

Open in new window

Aravind RanganathanWindows Application Developer

Author

Commented:
@Pawan Kumar that did not work either buddy :(
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
can you send some of your table data and expected output.
Daryl BamforthTechnical Expert

Commented:
Try

SELECT     [Unit UPC Base Item], [Item Description]
FROM         (SELECT     Top 1([Item Description]) AS [Item Description], [Unit UPC Base Item]
                       FROM          dbo.ItemDescLookups
                       GROUP BY [Unit UPC Base Item]) 
                       ORDER BY CountOfDescription DESC LIMIT 1
                     AS Sub

Open in new window

Aravind RanganathanWindows Application Developer

Author

Commented:
@ Pawan
so this is the data from the itemdesclookups:

Unit UPC Base Item      Item Description      CountOfDescription
205                                            Something      10
205                                            Something1      6
205                                            Something2      4
205                                       Something3      2
205                                            Something4      2
205                                            Something5      1
205                                            Something6      1
205                                            Something7      1
205                                            Something8      1
205                                            Something9      1
205                                            Something10      1

then i have a view created
SELECT     [Unit UPC Base Item], [Item Description]
FROM         (SELECT     MIN([Item Description]) AS [Item Description], [Unit UPC Base Item]
                       FROM          dbo.ItemDescLookups
                       GROUP BY [Unit UPC Base Item]) AS Sub

Open in new window

When i try Min something 10 gets returned and when i try max something5 gets returned.

so you need anymore info bud?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Output you need from the input you have given.
Aravind RanganathanWindows Application Developer

Author

Commented:
@ Pawan Kumar something the one with count of 10
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Please use this -

SELECT [Unit UPC Base Item],[Item Description],[CountOfDescription]
FROM 
(
	SELECT  * , ROW_NUMBER() OVER ( PARTITION BY [Unit UPC Base Item] ORDER BY CountOfDescription DESC) rnk
	FROM dbo.ItemDescLookups
)k WHERE rnk = 1

Open in new window

Aravind RanganathanWindows Application Developer

Author

Commented:
@Pavan Kumar worked beautifully exactly what i wanted. Thnx bud.
Aravind RanganathanWindows Application Developer

Author

Commented:
Thnx
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Welcome. Glad to help as always. :)
Aravind RanganathanWindows Application Developer

Author

Commented:
@Pavan Kumar how do i put this code in a view bud its throwing errors?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
CREATE VIEW VIEWName
AS
SELECT [Unit UPC Base Item],[Item Description],[CountOfDescription]
FROM 
(
	SELECT  * , ROW_NUMBER() OVER ( PARTITION BY [Unit UPC Base Item] ORDER BY CountOfDescription DESC) rnk
	FROM dbo.ItemDescLookups
)k WHERE rnk = 1

Open in new window