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 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](https://stackoverflow.com/questions/156114/best-way-to-get-result-count-before-limit-was-applied-in-php-postgresql/8242764#8242764)
* `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?](https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492)
* The numbers in `DISTINCT ON` and `GROUP BY` are just a syntactical shorthand notation for convenience. Similar:

	+ [PostgreSQL equivalent for MySQL GROUP BY](https://stackoverflow.com/questions/10398528/postgresql-equivalent-for-mysql-group-by/10398558#10398558)As are the added table aliases (syntactical shorthand notation).
* Basics for `DISTINCT ON`

	+ [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564#7630564)
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

```