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

Grouping can help in this case. 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 will not work.
Although window functions can solve the problem, they are not always convenient to use:

SELECT DISTINCT first_value(advertisement_id) OVER w AS advertisement_id,
   min(advertisement_date) OVER w AS advertisement_date,
   car_id, first_value(seller_account_id) OVER w AS seller_account_id
 FROM car_portal_app.advertisement
 WINDOW w AS (PARTITION BY car_id ORDER BY advertisement_date);

In the preceding code, DISTINCT is used to remove the duplicates that were grouped together in the previous example.
PostgreSQL provides an explicit way of selecting the first record within each group. The DISTINCT ON keywords are used for this. The syntax is as follows:

SELECT DISTINCT ON (<expression_list>) <Select-List>
...
ORDER BY <order_by_list>

In the preceding code snippet, for each distinct combination of values of expression_list, only the first record will be returned by the SELECT statement. The ORDER BY clause is used to define a rule to determine which record is the first.

expression_list from DISTINCT ON must be included in the order_by_list list.

For the task being discussed, it can be applied in the following way:

SELECT DISTINCT ON (car_id) advertisement_id, advertisement_date, car_id, seller_account_id
 FROM car_portal_app.advertisement
 ORDER BY car_id, advertisement_date;

This code is much clearer, easier to read and understand, and in fact works faster in most of the cases.