Aravind Ranganathan
asked on
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
Group by doesn't change the order.
Add
Add
order by [Unit UPC Base Item] desc
ASKER
@Pawan Kumar that is giving me the 6th record which has a countofdescription = 1
From where you are getting CountofDescription column.
So your wanting it to return the one with 10?
ASKER
@ Pawan the count of description is part of the table, i am not running any Count to get that.
ASKER
@ Daryl Banfort yes i want it to return the one with 10
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]
ASKER
@Pawan Kumar that did not work either buddy :(
can you send some of your table data and expected output.
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
ASKER
@ 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
so you need anymore info bud?
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
When i try Min something 10 gets returned and when i try max something5 gets returned.so you need anymore info bud?
Output you need from the input you have given.
ASKER
@ Pawan Kumar something the one with count of 10
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@Pavan Kumar worked beautifully exactly what i wanted. Thnx bud.
ASKER
Thnx
Welcome. Glad to help as always. :)
ASKER
@Pavan Kumar how do i put this code in a view bud its throwing errors?
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
use MAX. - You need to use the MAX function to get the Maximum value from a COLUMN
Open in new window
Another option is you can use a ROW_NUMBER function.