Advanced SQL

In the following sections, some other advanced SQL techniques will be introduced:

• The DISTINCT ON clause, which helps finding the first records in groups

• The set returning functions, which are functions that return relations

• LATERAL joins, which allow subqueries to reference each other

• Some special aggregating functions

Selecting the first records

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 will require 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;...