An alternative to DISTINCT
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.
Related articles:
Filed in Oracle, Tips on 08 Mar 06 | Tags: sql
Cool trumps simple every time!
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.
Why don’t you simply write: select distinct department_id from employees order by department_id;
Petri, sure you can simply write that too.
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
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.
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!