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

PostgreSQLDatabaseData Storage

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

nameroll_nosubjecttotal_marksmarks_obtained
Anil1English10056
Anil1Math10065
Anil1Science10045
Roy2English10078
Roy2Math10098
Roy2Science10067

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 −

nameroll_no
Roy2
Anil1

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 −

student_namestudent_id
Anil1
Roy2

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 −

nameroll_nosubjecttotal_marksmarks_obtained
Roy2Science10067
Roy2Math10098
Roy2English10078
Anil1Science10045
Anil1Math10065
Anil1English10056

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.

raja
Published on 02-Feb-2021 12:57:04
Advertisements