Select max record of each group on a group by

Question

I'm using PostgreSQL. I need to select the max of each group, the situation is that the table represents the products sell on each day, and I want to know the top sold product of each day.

SELECT sum(detalle_orden.cantidad) as suma,detalle_orden.producto_id as producto
      ,to_char(date_trunc('day',orden.fecha AT TIME ZONE 'MST'),'DY') as dia
FROM detalle_orden
LEFT JOIN orden ON orden.id = detalle_orden.order_id
GROUP BY orden.fecha,detalle_orden.producto_id 
ORDER BY dia,suma desc

Is returning:

suma  producto  dia
4     1         FRI
1     2         FRI
5     3         TUE
2     2         TUE

I want to get:

suma  producto  dia
4     1         FRI
5     3         TUE

Only the top product of each day (with the max(suma) of each group).

I tried different approaches, like subqueries, but the aggregate function used make things a bit difficult.

`Only the top product of each day.( with the max(suma) of each group)` It's a common oversight that more than one product may tie for the highest value. You need to define *exactly* what you want to happen in such cases. — Erwin Brandstetter, May 10 '15 at 23:42

score 2 · Answer 1 · answered May 10 '15 at 22:37

2

You can (ab)use SELECT DISTINCT ON with the appropriate ordering clause. Assuming you made your previous query into a view:

SELECT DISTINCT ON (dia, producto) * FROM some_view ORDER BY dia, producto, suma DESC;

the DISTINCT ensures you will retain only one row for every day and product, and the ORDER BY ensures it retains the correct one

answered May 10 '15 at 22:37

b0fh

1,678
12
28

Thanks, but I need to pass the full sql in the query,becouse if not I need to generate the view first. – Ricardo Umpierrez May 10 '15 at 22:48
Use a subquery then, shouldn't be an issue ? – b0fh May 10 '15 at 22:50

score 1 · Answer 2 · answered May 10 '15 at 22:40

By the windowing function: RANK you can easely get it:

select * from
(
select suma,producto,dia, rank() over (partition by dia order by suma desc) as ranking
from your_query
)A
where ranking = 1

So you final query will be something like:

select * from
(
select suma,producto,dia, rank() over (partition by dia order by suma desc) as ranking
from 
(
SELECT sum(detalle_orden.cantidad) as suma,detalle_orden.producto_id as     producto,to_char(date_trunc
    ('day',orden.fecha AT TIME ZONE 'MST'),'DY') as dia FROM detalle_orden     LEFT JOIN
    orden ON orden.id= detalle_orden.order_id GROUP by
    orden.fecha,detalle_orden.producto_id ) B
) A
where ranking = 1

score 1 · Accepted Answer · edited May 23 '17 at 11:51

You can still use DISTINCT ON to get this done in a single query level without subquery, because DISTINCT is applied after GROUP BY and aggregate functions (and after window functions):

SELECT DISTINCT ON (3)
       sum(d.cantidad) AS suma
     , d.producto_id AS producto
     , to_char(o.fecha AT TIME ZONE 'MST', 'DY') AS dia
FROM   detalle_orden d
LEFT   JOIN orden o ON o.id = d.order_id
GROUP  BY o.fecha, d.producto_id 
ORDER  BY 3, 1 DESC NULLS LAST, d.producto_id;

Notes

This solution returns exactly one row per dia (if available). if multiple products tie for top sales my arbitrary (but deterministic and reproducible) pick is the one with the smaller producto_id.
If you need all peers tying for one day use rank() as suggested by @Houari.
The sequence of events in an SQL SELECT query is explained in this related answer:
- Best way to get result count before LIMIT was applied
date_trunc() was just noise in the calculation of dia. I removed it.
I added NULLS LAST to the descending sort order since it is unclear whether there might be rows with NULL for suma in the result:
- PostgreSQL sort by datetime asc, null first?
The numbers in DISTINCT ON and GROUP BY are just a syntactical shorthand notation for convenience. Similar:
- PostgreSQL equivalent for MySQL GROUP BY
As are the added table aliases (syntactical shorthand notation).
Basics for DISTINCT ON
- Select first row in each GROUP BY group?

Selected this,because explains a bit more, may be useful for other people that searchs the same. — Ricardo Umpierrez, May 11 '15 at 00:18

Select max record of each group on a group by

3 Answers3

Notes