O'Reilly logo

Learning PostgreSQL by Andrey Volkov, Achim Vannahme, Salahaldin Juba

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required