Group By Giving me the Wrong Data

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

Select allOpen in new window

Another option is you can use a ROW_NUMBER function.

Group by doesn't change the order.

Add

order by [Unit UPC Base Item]  desc

Select allOpen in new window

@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?

@ Pawan the count of description is part of the table, i am not running any Count to get that.

@ 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] 

Select allOpen in new window

@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

Select allOpen in new window

@ 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

Select allOpen 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?

Output you need from the input you have given.

@ Pawan Kumar something the one with count of 10

@Pavan Kumar worked beautifully exactly what i wanted. Thnx bud.

Thnx

Welcome. Glad to help as always. :)

@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

Select allOpen in new window