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.