Sometimes we need maximums of each group, or minimums of each group in SQL. In PostgeSQL, you can use DISTINCT ON statement to achieve this.

In PostgreSQL’s documentation DISTINCT ON statement explained as:

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.

Since PostgreSQL engine can not predict the first row it is better to include order by statement in the end of our queries.

SELECT DISTINCT ON (location) location, time, report FROM weather_reports ORDER BY location, time DESC;

The query above (taken from PostgeSQL documentation) will retrieve most recent wheather report.

Where to Use

DISTINCT ON statement is useful for reporting purposes, when you express your requirement like “highest sale of each seller”, “recent marks of each student”, “last lowest production rate of each factory”.

DISTINCT ON statement is also useful for alerts and business rule related querying.

Tip of the Day! Use Indexes

After summarizing DISTINCT ON to select first row in each group i need to add one more point.

If you run a lot of DISTINCT ON queries you need to add indexes to the fields you order. Consider defining multi column indexes.

CREATE INDEX wheather_reports_x ON wheather_reports (location,time);

Statement above will create an index on two columns simultaneously, which will help you to run DISTINCT ON queries with less cost.

Today i tried to explain how to use DISTINCT ON statement to select first row of each group. If you have comments or something to add, or any question, please leave a comment below.