Saturday, March 29, 2008

Adding custom DISTINCT ON behavior

In a typical query we sometimes use Distinct keyword to eliminate duplicate rows.

SELECT DISTINCT column1, column2, column3.... FROM table1 WHERE ....

This elimination of duplicate rows occurs after the result table has been generated & the selected columns has been processed (if needed)

Two rows are considered distinct if they differ in at least one column value.
Null values are considered equal in this case.

But this default & obvious consideration can be customized. How? Ok..let me explain with an example.

Say we have a table named Student

Name Bengali English Math
Alex 70 85 96
Barnabus 96 70 85
Alfred 96 70 84

Say, you want to consider those students as distinct who has different total marks of Bengali, English & Math.
So the first two students are not distinct, the first & third are distinct and the 2nd & third are distinct.

SELECT DISTINCT ON (Bengali+English+Math) Name
FROM Student;

Now think yourself about a case where you can apply this custom behavior of Distinct and if you find any then don't hesitate to let me know ;P

No comments: