Group By Giving me the Wrong Data

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.
Aravind RanganathanWindows Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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.
0
Daryl BamforthTechnical ExpertCommented:
Group by doesn't change the order.

Add

order by [Unit UPC Base Item]  desc

Open in new window

0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Pawan Kumar that is giving me the 6th record which has a countofdescription = 1
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Pawan KumarDatabase ExpertCommented:
From where you are getting CountofDescription column.
0
Daryl BamforthTechnical ExpertCommented:
So your wanting it to return the one with 10?
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@ Pawan the count of description is part of the table, i am not running any Count to get that.
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@ Daryl Banfort yes i want it to return the one with 10
0
Pawan KumarDatabase ExpertCommented:
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

0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Pawan Kumar that did not work either buddy :(
0
Pawan KumarDatabase ExpertCommented:
can you send some of your table data and expected output.
0
Daryl BamforthTechnical ExpertCommented:
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

0
Aravind RanganathanWindows Application DeveloperAuthor 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?
0
Pawan KumarDatabase ExpertCommented:
Output you need from the input you have given.
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@ Pawan Kumar something the one with count of 10
0
Pawan KumarDatabase ExpertCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Pavan Kumar worked beautifully exactly what i wanted. Thnx bud.
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
Thnx
0
Pawan KumarDatabase ExpertCommented:
Welcome. Glad to help as always. :)
0
Aravind RanganathanWindows Application DeveloperAuthor Commented:
@Pavan Kumar how do i put this code in a view bud its throwing errors?
0
Pawan KumarDatabase ExpertCommented:
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

1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.