Learning PostgreSQL 11 - Third Edition

Selecting the first records

Quite often, it's necessary to find the first records based on some criteria. For example, let's take the car_portal database; suppose you need to find the earliest advertisement for each car_id in the advertisement table.

In this case, grouping can help. It requires a subquery to implement the logic:

SELECT advertisement_id, advertisement_date, adv.car_id, seller_account_id FROM car_portal_app.advertisement adv INNER JOIN (   SELECT car_id, min(advertisement_date) min_date    FROM car_portal_app.advertisement GROUP BY car_id ) first    ON adv.car_id=first.car_id AND adv.advertisement_date = first.min_date;

However, if the ordering logic is complex and cannot be implemented using the min function, this approach won't ...