How to apply DISTINCT constraint on select columns in queries in PostgreSQL?

Suppose you have a table exam_scores containing 5 columns. An example is given below with some dummy data.

Now, one student could have sat for exams of multiple subjects, and therefore, there are multiple rows for 1 student. If you wish to find out the total number of students in the class, you may want to find the number of distinct values of roll_no. You can apply the distinct constraint on a specific column as follows −

SELECT DISTINCT ON (roll_no) name, roll_no
FROM exam_scores
ORDER BY roll_no DESC

Here’s what the output of the above query will look like −

You can also apply a distinct constraint on column aliases.

SELECT DISTINCT ON (student_id) name as student_name, roll_no as
student_id
FROM exam_scores
ORDER BY student_id ASC

The output of the above query will look like this −

A DISTINCT constraint can also be applied to multiple columns. Suppose you want to find the number of distinct answer sheets received. That will be equal to the number of distinct values of (roll_no, subject) combinations.

SELECT DISTINCT ON (roll_no, subject) name, roll_no, subject
FROM exam_scores
ORDER BY roll_no, subject, name DESC

The output of this query is given below −

Please note that it is important that the ORDER BY expression has the same column order as the DISTINCT ON expression. ORDER BY expression can have more columns after the DISTINCT ON columns are covered.

Yash Sanghvi

Updated on: 02-Feb-2021

107 Views

Kickstart Your Career

Get certified by completing the course

Get Started