Group By Giving me the Wrong Data

Aravind Ranganathan
Aravind Ranganathan used Ask the Experts™
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial