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.

Answer: 
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
By the windowing function: [RANK](http://www.postgresql.org/docs/9.4/static/functions-window.html) 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

```