Alternative Distinct dan Group By

Sumur : Distinct Alternative

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.