Click here to Skip to main content
15,567,221 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I have table like this
Id 	 ProductName	MainCategory	 AdditionalCategory	size 
1	 American Roast	Coffee		                        10
2	 American Roast	Coffee		                        12
3        American Roast	Coffee		                        14
4	 American Roast	Coffee		                        16
5	 American Roast	Coffee	          Hawaiian	        10
6	 American Roast	Coffee	          Hawaiian	        12
7	 American Roast	Coffee	          Hawaiian	        14
8	 American Roast	Coffee	          Hawaiian	        16
9	 American Roast	Coffee	          Kona	                10
10	 American Roast	Coffee	          Kona	                12
11	 American Roast	Coffee	          Kona	                14
12	 American Roast	Coffee	          Kona	                16

I have use group by class in this table

select Productname,Maincategory,AdditionalCategory1 from testproducts
group by Productname,Maincategory,AdditionalCategory1 

I got Result like this

ProductName	MainCategory	 AdditionalCategory
American Roast    Coffee  
American Roast    Coffee         Hawaiian
American Roast    Coffee         Kona

But I want Result

Id ProductName MainCategory     AdditionalCategory  Size 
 1  American Roast    Coffee                          10
 5  American Roast    Coffee         Hawaiian         10
 9  American Roast    Coffee         Kona             10

If any possible please give me code

Thanks Advance

If you want the size, then you should include the column in your query :)
And since you seem to only want the minimum size value, then you have to use the MIN() function.
SELECT Productname, Maincategory, AdditionalCategory, MIN(Size) FROM testproducts
GROUP BY Productname, Maincategory, AdditionalCategory

Hope this helps.
Share this answer
Murugesan22 14-May-14 7:37am    
I want Id also If it is possible?
phil.o 14-May-14 7:47am    
Then include it in the SELECT clause.
Maciej Los 14-May-14 12:20pm    
So, add it and check the results ;)
Maciej Los 14-May-14 12:43pm    
Please see my answer for details ;)
Try this:
DECLARE @tmp AS TABLE (Id INT IDENTITY(1,1), ProductName VARCHAR(30), MainCategory VARCHAR(30), AdditionalCategory VARCHAR(30), size INT)

INSERT INTO @tmp (ProductName, MainCategory, AdditionalCategory, size)
VALUES('American Roast', 'Coffee', NULL, 10),
('American Roast', 'Coffee', NULL, 12),
('American Roast', 'Coffee', NULL, 14),
('American Roast', 'Coffee', NULL, 16),
('American Roast', 'Coffee', 'Hawaiian', 10),
('American Roast', 'Coffee', 'Hawaiian', 12),
('American Roast', 'Coffee', 'Hawaiian', 14),
('American Roast', 'Coffee', 'Hawaiian', 16),
('American Roast', 'Coffee', 'Kona', 10),
('American Roast', 'Coffee', 'Kona', 12),
('American Roast', 'Coffee', 'Kona', 14),
('American Roast', 'Coffee', 'Kona', 16)

SELECT t2.Id, t1.ProductName, t1.MainCategory, t1.AdditionalCategory, t1.size
    SELECT ProductName, MainCategory, AdditionalCategory, MIN(size) AS size
    FROM @tmp
    GROUP BY ProductName, MainCategory, AdditionalCategory
) AS t1 LEFT JOIN @tmp AS t2 ON t1.ProductName = t2.ProductName AND t1.MainCategory = t2.MainCategory
        AND COALESCE(t1.AdditionalCategory, '-') = COALESCE(t2.AdditionalCategory,'-') AND t1.size  = t2.size

1	American Roast	Coffee	NULL	10
5	American Roast	Coffee	Hawaiian	10
9	American Roast	Coffee	Kona	10

You can't add Id together with MIN() function, because it enforces to add Id to GROUP BY list. The only way to achieve expected output is to use subquery.
Share this answer
Added Id to sol1 .

Select Min(id), ProductName,MainCategory,AdditionalCategory,MIN(size)
from testproducts
Group by ProductName,MainCategory,AdditionalCategory
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900