Select distinct on one column, returning multiple rows and columns

Question

With this query

SELECT 
    product, customer, sum(sales) as topSales 
FROM 
    invoices 
WHERE 
    product in (//lots of product id's here)
GROUP BY 
    product, customer 
ORDER BY 
    topSales DESC

I get a result set containing all buyers for a product, in order of sales descending.

PRODUCT  CUSTOMER    topSales
=============================
banana   Chris       50.35
banana   Eric        34.87
cookie   Emmy        54.54
apple    John        78.67
banana   Derek       33.87
banana   Sally       21.76
apple    Henry       65.78
cookie   Yoyo        24.67
milk     Chris       30.43
milk     Henry       22.43

I only want the top buyer for each product, it should look like this

PRODUCT  CUSTOMER    topSales
=============================
banana   Chris       50.35
cookie   Emmy        54.54
apple    John        78.67
milk     Chris       30.43

How can I get the result like this? I need to get distinct products, but only for the top buyer.

couple of other similar posts http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group and http://stackoverflow.com/questions/20883539/select-top-1-from-each-group-sql — Ric, Jan 08 '16 at 16:32

score 2 · Accepted Answer · answered Jan 08 '16 at 16:34

2

You can do this using window funtions:

SELECT *
FROM (SELECT product, customer, sum(sales) as topSales,
             ROW_NUMBER() OVER (PARTITION BY product ORDER BY SUM(sales) DESC) as seqnum
      FROM invoices 
      WHERE product in(//lots of product id's here)
      GROUP BY product, customer
     ) pc
WHERE seqnum = 1
ORDER BY topSales DESC;

answered Jan 08 '16 at 16:34

Gordon Linoff

1,242,037
58
646
786

1

Thanks Gordon. So this query partitions the rows into groups by product , and assigns row numbers. Then you select from each group where row num = 1? – Eric Guan Jan 08 '16 at 16:57
@EricGuan: you've totally nailed the feature :-) Yes, exactly, that's what T-SQL is doing. Very handy and very useful! – marc_s Jan 08 '16 at 17:11

score 1 · Answer 2 · edited Apr 13 '17 at 12:42

From: https://dba.stackexchange.com/questions/1002/how-to-get-the-max-row
(see link for more examples of how you can get your top row)

--ranking + derived table
SELECT
    C.*
FROM
    (SELECT ROW_NUMBER() OVER (PARTITION BY product ORDER BY topSales ) AS topBuyer,
product, customer, sum(sales) as topSales FROM invoices 
WHERE product in(//lots of product id's here)
GROUP BY product,customer ORDER BY topSales DESC) C
WHERE
    C.topBuyer = 1
ORDER BY
    C.product

score 0 · Answer 3 · answered Jan 09 '16 at 05:26

We can achieve the same result by using IN condition and MAX also

DECLARE @Invoices TABLE 
    (PRODUCT varchar(6), CUSTOMER varchar(5), topSales decimal(18,2))
;

INSERT INTO @Invoices
    (PRODUCT, CUSTOMER, topSales)
VALUES
    ('banana', 'Chris', 50.35),
    ('banana', 'Eric', 34.87),
    ('cookie', 'Emmy', 54.54),
    ('apple', 'John', 78.67),
    ('banana', 'Derek', 33.87),
    ('banana', 'Sally', 21.76),
    ('apple', 'Henry', 65.78),
    ('cookie', 'Yoyo', 24.67),
    ('milk', 'Chris', 30.43),
    ('milk', 'Henry', 22.43)
;
select PRODUCT, CUSTOMER, topSales from @Invoices  t 
where t.topSales in 
(select MAX(topSales) 
from @Invoices 
where PRODUCT = t.PRODUCT) --AND  product in (//lots of product id's here)

Select distinct on one column, returning multiple rows and columns

3 Answers3