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!!

Answer 1

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

Answer 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

Answer 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