SQL: How to get a every maximum value of a table?

Question

I have a huge table called 'Sales` with these areas

Time  | Product  | amount | customerid
Date1 | Product1 | 2      | 2
Date1 | Product2 | 5      | 3
Date1 | Product1 | 6      | 3
Date1 | Product4 | 1      | 3
Date2 | Product1 | 2      | 1
Date2 | Product1 | 2      | 4
Date2 | Product3 | 3      | 4
Date2 | Product3 | 3      | 1

Now, I want to get a most popular product each day.

SELECT  Time, 
        Product, 
        SUM(amount) AS Total 
FROM Sales 
     GROUP BY Time, Product

Gives me the following

Time  | Product  | total
Date1 | Product1 | 8
Date1 | Product2 | 5
Date1 | Product4 | 1
Date2 | Product1 | 4
Date2 | Product4 | 6

In which way I have to change my query, to get just the top seller products of each day?

http://stackoverflow.com/questions/4448504/mysql-group-by-and-sort-each-group OR `SELECT Time, Product, amount AS Total FROM Sales Order by time DESC, amout desc` — Jan Czarny, Nov 20 '13 at 09:15
Hint: Try googling the tags you assigned, especially that last one. — Basil Bourque, Nov 20 '13 at 09:16

score 3 · Answer 1 · answered Nov 20 '13 at 09:28

3

Try this query. Rank function implementation is different in different DB's.

SELECT TIME, PRODUCT
FROM (
SELECT TIME, PRODUCT, RANK() OVER(ORDER BY SUM_AMOUNT) AS SELECT_TOP
FROM 
(
(
      SELECT 
      TIME
     , PRODUCT
     , SUM(AMOUNT) AS SUM_AMOUNT
     FROM Sales 
GROUP BY TIME, PRODUCT ) TBL1 ) TBL2
WHERE
SELECT_TOP = 1

answered Nov 20 '13 at 09:28

Prashanth

1,294
3
13
30

1

+1 (although you have a minor syntax error - check parenthesis count). Note that, as the OP hasn't currently listed his RDBMS, this _might_ not work in his situation; however most of the larger vendors can handle this. Also, depending on how the OP wants to handle ties (...also unspecified...), `RANK()` may need to be switched out for one of the other functions. – Clockwork-Muse Nov 20 '13 at 12:06
Hi Prashanth. I am using PostgreSQL und your advice doesn't work in a correct way. It shows me not all the dates I have in my table. – user3012325 Nov 20 '13 at 14:16

code writer · Answer 2 · 2013-11-20T09:19:00.773

1

SELECT Time, Product, SUM(amount) AS Total FROM Sales GROUP BY Time, Product order by sum(amount) DESC;

edited Nov 20 '13 at 09:19

answered Nov 20 '13 at 09:15

code writer

11
3

The asker just want the topseller products – david Nov 20 '13 at 09:18

score 1 · Answer 3 · answered Nov 20 '13 at 09:15

1

SELECT Time, Product, SUM(amount) AS Total FROM Sales GROUP BY Time, Product ORDER BY Total DESC limit 1

I use ORDER BY Total DESC to sort Total column by descending so the largest value of Total column is in the top. Therefore, I just take the largest one by limit 1

answered Nov 20 '13 at 09:15

david

3,225
9
30
43

`SELECT Time, Product, SUM(amount) AS Total FROM Sales GROUP BY Time, Product ORDER BY time Desc, Total DESC` – Jan Czarny Nov 20 '13 at 09:24
@Jan Czarny instead of comment here, please to add another answer – david Nov 20 '13 at 09:27

score 1 · Answer 4 · answered Nov 20 '13 at 09:27

1

Query:

SELECT 
       Time, Product, SUM(amount) AS Total 
FROM Sales 
       GROUP BY Time, Product 
       ORDER BY time Desc, Total DESC

answered Nov 20 '13 at 09:27

Jan Czarny

916
1
11
29

SQL: How to get a every maximum value of a table?

4 Answers4