Tutorial :SQL Query using distinct



Question:

hello i have a table with the following structure:

sku, category, brand, product_name, inventory_count  

with the following data:

1001, car, honda, "honda car 1", 5  1002, truck, honda, "honda truck 1", 6  1003, car, ford, "ford car 1", 7  1004, truck, ford, "ford truck 1", 8  1005, bike, honda, "honda bike 5", 9  1006, bike, ford, "ford bike 6", 10  

I'm using the following SQL query

select distinct category from products  

this would return the following:

car  truck  bike  

this works great,

Now I want to get just one product example for each of the categories with the greatest INVENTORY_COUNT

so that it returns the data as:

car, "ford car 1"  truck, "ford truck 1"  bike, "ford bike 6"  

what SQL query would i run to get that data??

i want the item with the greater INVENTORY_COUNT for each category

thanks!!


Solution:1

SELECT category, MAX(product_name)  FROM table  GROUP BY    category  


Solution:2

Even you can try this (Sql Server 2005+)

select x.category,x.product_name from(  select ROW_NUMBER() over(partition by category order by product_name) rn,  t.* from @t t) x  where x.rn = 1    **category         product_name**    bike                ford bike 6  car              ford car 1  truck              ford truck 1  

If u use x.rn = 2 the output is

category         product_name  bike             honda bike 5  car           honda car 1  truck           honda truck 1  


Solution:3

Judging from the name inventory_count I assume that the value is not unique, so there could be more than one product in the category with the same count. Therefore you can't use the count as identifier in a join, so you need a subquery that limits the result to a single item.

select    p.category,    product_name = (      select top 1 n.product_name      from products n      where n.category = p.category      order by n.inventory_count desc    )  from products p  group by p.category  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »