Display distinct multiple columns with SQL

 

Question:  I need to display distinct records of each column within a single table.  How do you write a SQL to select each distinct column within a table without using a function?

Answer: By Laurent Schneider, from his book ?Advanced Oracle SQL Programming?.  In this query we start by selecting the distinct values for all columns.  Next, we issue SQL*plus breaks to get only the distinct values for every column in the result set:

select
   deptno, loc, job, sal, ename
from
   lsc_emp
join
   lsc_dept
using (deptno)
order by
   deptno,loc,job,sal,ename;


    DEPTNO LOC           JOB              SAL ENAME
---------- ------------- --------- ---------- ----------
        10 NEW YORK      CLERK           1300 MILLER
        10 NEW YORK      MANAGER         2450 CLARK
        10 NEW YORK      PRESIDENT       5000 KING
        20 DALLAS        ANALYST         3000 FORD
        20 DALLAS        ANALYST         3000 SCOTT
        20 DALLAS        CLERK            800 SMITH
        20 DALLAS        CLERK           1100 ADAMS
        20 DALLAS        MANAGER         2975 JONES
        30 CHICAGO       CLERK            950 JAMES
        30 CHICAGO       MANAGER         2850 BLAKE
        30 CHICAGO       SALESMAN        1250 MARTIN
        30 CHICAGO       SALESMAN        1250 WARD
        30 CHICAGO       SALESMAN        1500 TURNER
        30 CHICAGO       SALESMAN        1600 ALLEN


SQL> bre on deptno nodup on loc nodup on job nodup
SQL> /
    DEPTNO LOC           JOB              SAL ENAME
---------- ------------- --------- ---------- ----------
        10 NEW YORK      CLERK           1300 MILLER
                         MANAGER         2450 CLARK
                         PRESIDENT       5000 KING
        20 DALLAS        ANALYST         3000 FORD
                                         3000 SCOTT
                         CLERK            800 SMITH
                                         1100 ADAMS
                         MANAGER         2975 JONES
        30 CHICAGO       CLERK            950 JAMES
                         MANAGER         2850 BLAKE
                         SALESMAN        1250 MARTIN
                                         1250 WARD
                                         1500 TURNER
                                         1600 ALLEN

 

---