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:
Hi,

I have table like this
SQL
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


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



I got Result like this

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



But I want Result

XML
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
Posted

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.
SQL
SELECT Productname, Maincategory, AdditionalCategory, MIN(Size) FROM testproducts
GROUP BY Productname, Maincategory, AdditionalCategory

Hope this helps.
 
Share this answer
 
Comments
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:
SQL
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
FROM (
    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


Result:
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