You want to select distinct values from a column in a table. No problem. You use DISTINCT or GROUP BY in your query. But did you know that there is a third way to suppress duplicates? For example:
Both
select distinct department_id
from employees
and
select department_id
from employees
group by department_id
return
DEPARTMENT_ID
----------------------
100
30
90
20
70
110
50
80
40
60
10
12 rows selected
Boring. Nothing new here.
But wait a minute, here is what I learnt today, another way to return distinct department ids, using the function ROW_NUMBER:
select department_id
from (
select department_id,
row_number( ) over
(partition by department_id
order by department_id) rownumber
from employees
) t
where rownumber = 1
returns
DEPARTMENT_ID
----------------------
10
20
30
40
50
60
70
80
90
100
110
12 rows selected
It’s the same output as the first two queries, except that it is ordered differently.
To be honest, I will continue using DISTINCT or GROUP BY for such requirements, however, it does not hurt to know that there are alternatives.
Possibly related:
- DISTINCT vs GROUP BY
- A Blogger.com Alternative
- del.icio.us direc.tor on AJAX
- CURRVAL and NEXTVAL
- Cool Firefox extension: Foxmarks
Tagged sql | Post a Comment


















Cool trumps simple every time!
March 9th, 2006, at 6:26 am #This post is a perfect example of cool versus simple. However, sometimes, I do find some cool and simple solutions to some not so simple problems.
March 9th, 2006, at 9:07 am #Why don’t you simply write: select distinct department_id from employees order by department_id;
April 13th, 2006, at 3:36 am #Petri, sure you can simply write that too.
April 13th, 2006, at 6:17 pm #select distinct is the easy way,however,it can prove to be slow compared to other methods specialy on large tables.
read more about this here
July 2nd, 2006, at 10:53 pm #Mostafa, the link you provided is for PostgreSQL not for Oracle. I do not know PostgreSQL so I cannot intelligently comment on it. However, I do know one thing, instead of saying: “using a SELECT DISTINCT SQL query is not a good idea”, I would say: “use distinct wherever and whenever you have to.
July 3rd, 2006, at 8:42 am #Cool post! Just had an issue with a query for which both group by and distinct didn’t work.
Or at least, not the way I wanted it too ;). This solution worked fine though, since it literally enforces the uniqueness.
Very nice!
October 12th, 2007, at 3:14 am #