How to get distinct values without using Distinct Keyword - Interview Questions Part 3

How to get distinct values without using Distinct Keyword:-

Generally we use DISTINCT keyword to get distinct values/to filter duplicate values.

Alternative for DISTINCT:

Lets take following table with data for this scenario:

SQL>  select * from dept1;
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
    40 OPERATIONS     BOSTON
    40 medicine       LONDON

We generally use as below.

SQL>  select distinct deptno from dept1;
DEPTNO
------
    30
    20
    40
    10

1st Alternative: Using Group By 

SQL>  select deptno from dept1 group by deptno;
DEPTNO
------
    30
    20
    40
    10

2ndAlternative: Using Row_number analytical function

 SQL> select * from (select deptno, row_number()over(partition by deptno order by 1) rn from dept1) where rn=1 ;

DEPTNO         RN
------ ----------
    10          1
    20          1
    30          1
    40          1

 3rd Method: Using SET operator (i.e UNION)

SQL>  select deptno from dept1
       union
      select deptno from dept1
 
DEPTNO
------
    10
    20
    30
    40


Interview Questions Part1: Check out here ---> Interview Questions Part1

Interview Questions Part2: Check out here ---> Interview Questions Part-2