Advanced SQL techniques

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

• The DISTINCT ON clause, which helps to group records and take the first record for each group
• Selecting sample data from a very big table
• Set-returning functions, which are functions that return relations
• LATERAL joins, which allow subqueries to reference each other
• Advanced grouping techniques that can be used to generate reports
• Some special aggregating functions

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