Last row in a history table

When writing analytical query-s where you need to get the last rows in the table per user there is a nice feature in PostgreSQL that doesn’t need you to rely on subqueries. The following query selects the last event for every user in a status history table:

select distinct on (key_user) * from order_status_log where key_status=7 order by key_user, deliver_time desc;
explain:
Unique  (cost=48603.10..50390.54 rows=109180 width=78)
    ->  Sort  (cost=48603.10..49496.82 rows=357487 width=78)
    Sort Key: key_user, deliver_time
        ->  Seq Scan on user_number  (cost=0.00..15629.34 rows=357487 width=78)
        Filter: (key_status = 7)

Note that the columns that are used for distinct must also be in the order by clause!

Advertisements

About this entry