While working with databases, users often come across a situation where they need to select one row from each group. PostgreSQL provides a “SELECT DISTINCT ON” statement to deal with such scenarios. Using this statement, users can get a random row, or a specific row, from each group,

This post will walk you through the practical usage of the SELECT DISTINCT ON statement in PostgreSQL.

How to Select One Row From Each Group in PostgreSQL?

Use the Postgres’ “SELECT DISTINCT ON” statement to select one row per group. The stated statement selects and retrieves a random row from each group. However, the ORDER BY clause can be utilized along with the “SELECT DISTINCT ON” statement to get a specific row from each group. The syntax to use the stated command is demonstrated in the following snippet:

SELECT DISTINCT ON (col_name)
FROM tab_name
ORDER BY col_name ASC|DESC;

You can specify single or multiple columns in the SELECT DISTINCT ON statement.

Example: How to Use the SELECT DISTINCT ON Statement in Postgres?

In this example, we will utilize a sample table name “emp_info” that we have already created with the following records:

SELECT * FROM emp_info
ORDER BY emp_id;

Let’s suppose we want to select only one employee for each joining_date. For this purpose, we will utilize the following query:

SELECT DISTINCT ON (joining_date) joining_date, emp_id, emp_name
FROM emp_info;

The output proves that a random employee is selected for each joining date. Use the SELECT DISTINCT ON statement with the ORDER BY clause to select a specific employee from each group:

SELECT DISTINCT ON (joining_date) joining_date, emp_id, emp_name
FROM emp_info
ORDER BY joining_date, emp_id ASC;

The above query will select one row from each group but this time the employees will be selected from each group based on their ids:

That was all about selecting one row from each group in Postgres.

Conclusion

In PostgreSQL, the “SELECT DISTINCT ON” statement is used to select one row per group. The stated command selects and retrieves a random row from each group. However, the ORDER BY clause can be utilized along with the “SELECT DISTINCT ON” statement to get a specific row from each group. This post has presented an in-depth overview of selecting one row from each group in PostgreSQL.