SQL query getting data

Question

In SQL Server 2000:

hello i have a table with the following structure:

sku      brand     product_name       inventory_count
------   ------    -------------      ---------------
c001     honda     honda car 1         3
t002     honda     honda truck 1       6
c003     ford      ford car 1          7
t004     ford      ford truck 1        8
b005     honda     honda bike 5        9
b006     ford      ford bike 6        18

I'm using the following SQL query

select distinct left(sku,1) from products

this would return the following:

c
t
b

and then ...

c = car
t = truck
b = 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:

c, "ford car 1"
t, "ford truck 1"
b, "ford bike 6"

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

i want the item with the greatest INVENTORY_COUNT for each category.. left(sku,1)

thanks!!

Answer 1

For mysql:

SELECT LEFT(sku,1), product_name FROM Table1 GROUP BY LEFT(sku,1)

For MS SQL 2005 (maybe also works in 2000?):

SELECT LEFT(sku,1), MAX(product_name) FROM Table1 GROUP BY LEFT(sku,1)

Answer 2

You could join the table on itself to filter out the rows with less than maximum inventory:

select       left(a.sku,1), max(a.product_name), max(a.inventory_count)
from         YourTable a
left join    YourTable more_inv
on           left(a.sku,1) = left(more_inv.sku,1)
and          a.inventory_count < more_inv.inventory_count
where        more_inv.sku is null
group by     left(a.sku,1)

The WHERE condition on more_inv.sku is null filters out rows that don't have the highest inventory for their one letter category.

Once we're down to rows with the maximum inventory, you can use max() to get the inventory_count (it'll be the same for all rows) and another max() to get one of the products with the highest inventory_count. You could use min() too.

Answer 3

Using SQL Server 2005 you can try this

DECLARe @Table TABLE(
    sku VARCHAR(50), 
    brand VARCHAR(50), 
    product_name VARCHAR(50), 
    inventory_count INT
)

INSERT INTO @Table SELECT 'c001', 'honda', 'honda car 1', 3

INSERT INTO @Table SELECT 't002', 'honda', 'honda truck 1', 6

INSERT INTO @Table SELECT 'c003', 'ford', 'ford car 1', 7

INSERT INTO @Table SELECT 't004', 'ford', 'ford truck 1', 8

INSERT INTO @Table SELECT 'b005', 'honda', 'honda bike 5', 9

INSERT INTO @Table SELECT 'b006', 'ford', 'ford bike 6', 18


SELECT  LEFT(sku,1),
     product_name
FROM    (
      SELECT *,
        ROW_NUMBER() OVER( PARTITION BY LEFT(sku,1) ORDER BY inventory_count DESC) ORDERCOUNT
      FROm @Table
     ) SUB
WHERE   ORDERCOUNT = 1

OK Then you can try

SELECT  LEFT(sku,1),
      *
    FROm    @Table t INNER JOIN
      (
       SELECT LEFT(sku,1) c,
         MAX(inventory_count) MaxNum
       FROM @Table
       GROUP BY LEFT(sku,1)
      ) sub ON  LEFT(t.sku,1) = sub.c and t.inventory_count = sub.MaxNum

Answer 4

im using the following sql query which works,

SELECT DISTINCT left(field1,1) as cat , MAX(sku) as topproduct FROM products where inventory_count > 0 GROUP BY left(sku,1)

i just need to add in there an ..order by inventory_count

Answer 5

Try this

declare @t table (sku varchar(50),brand varchar(50),product_name varchar(50),inventory_count int)
insert into @t 

  select 'c001','honda','honda car 1',3 union all
  select 't002','honda','honda truck 1',6 union all
  select 'c004','ford','ford car 1',7 union all
  select 't004','ford','ford truck 1',8 union all
  select 'b005','honda','honda bike 5',9 union all
  select 'b006','ford','ford bike 6',18

Query:

select 

x.s + space(2) + ',' + space(2) + '"' + t.product_name  + '"' as [Output] 

from @t t
inner join
(
    SELECT left(sku,1) as s,MAX(inventory_count) ic from @t
    group by left(sku,1)
) x
on x.ic = t.inventory_count
--order by t.inventory_count desc

Output

c  ,  "ford car 1"
t  ,  "ford truck 1"
b  ,  "ford bike 6"

Answer 6

In general, might there not be more than one item with max(inventory_count)?

To get max inventory per cateogry, use a subquery, (syntax will depend on your database):

SELECT LEFT(sku,1) as category, MAX(inventory_count) as c
FROM Table1
GROUP BY LEFT(sku,1)
SORT BY LEFT(sku,1)

This will give you a table of max_inventory by category, thus:

b,18
c,7
t,8

So now you know the max per category. To get matching products, use this result as a subquery and find all products in the given cateogry that match the given max(inventory_count):

SELECT t1.*
FROM Table1 AS t1,
(SELECT LEFT(sku,1) AS category, MAX(inventory_count) AS c
 FROM Table1
 GROUP BY LEFT(sku,1)
) AS t2
WHERE LEFT(t1.sku,1) = t2.category AND t2.c = t1.inventory_count

Sorry, the code above may/may not work in your database, but hope you get the idea.

Bill

PS -- probably not helpful, but the table design isn't really helping you here. If you have control over the schema, would help to separate this into multiple tables.