SQL SELECT DISTINCT

SQL SELECT DISTINCT statement is use for eliminate duplicates rows from selected column in table. You can add DISTINCT statement in multiple column.

SELECT with DISTINCT on all columns

Now we are execute SELECT query with DISTINCT on all columns. DISTINCT all column means same table row value eliminate duplicates rows.

In this example line number 7 or 13 row are same so two of one row automatically eliminate.

Syntax

SELECT DISTINCT * FROM table_name;

Example

SQL> SELECT * FROM userinfo;

  NO NAME                          ADDRESS
---- ----------------------------- ---------------------------
   1 Opal Kole                     63 street Ct.
   2 Max Miller                    41 NEW ROAD.
   3 Beccaa Moss                   2500 green city.
   4 Paul Singh                    1343 Prospect St
   5 Ken Myer                      137 Clay Road
   6 Jack Evans                    1365 Grove Way
   7 Opal Kole                     63 street Ct.
   8 Max Miller                    41 NEW ROAD.
   3 Beccaa Moss                   2500 green city.

9 rows selected.

SQL> SELECT DISTINCT * FROM userinfo;

  NO NAME                          ADDRESS
---- ----------------------------- ---------------------------
   3 Beccaa Moss                   2500 green city.
   6 Jack Evans                    1365 Grove Way
   1 Opal Kole                     63 street Ct.
   2 Max Miller                    41 NEW ROAD.
   5 Ken Myer                      137 Clay Road
   8 Max Miller                    41 NEW ROAD.
   4 Paul Singh                    1343 Prospect St
   7 Opal Kole                     63 street Ct.

8 rows selected.

SELECT with DISTINCT on two columns

Now we are execute SELECT statement with DISTINCT on two columns. DISTINCT two column means eliminate same value when both column have same value.

In this example line number 5,6 or 11,12 name and address column value are same so this both row automatically eliminate.

Syntax

SELECT DISTINCT column_name1, column_name2, ... FROM table_name;

Example

SQL> SELECT * FROM userinfo;

  NO NAME                          ADDRESS
---- ----------------------------- ---------------------------
   1 Opal Kole                     63 street Ct.
   2 Max Miller                    41 NEW ROAD.
   3 Beccaa Moss                   2500 green city.
   4 Paul Singh                    1343 Prospect St
   5 Ken Myer                      137 Clay Road
   6 Jack Evans                    1365 Grove Way
   7 Opal Kole                     63 street Ct.
   8 Max Miller                    41 NEW ROAD.
   3 Beccaa Moss                   2500 green city.

9 rows selected.

SQL> SELECT DISTINCT name, address FROM userinfo;

NAME                               ADDRESS
---------------------------------- ---------------------------
Opal Kole                          63 street Ct.
Paul Singh                         1343 Prospect St
Ken Myer                           137 Clay Road
Beccaa Moss                        2500 green city.
Max Miller                         41 NEW ROAD.
Jack Evans                         1365 Grove Way

6 rows selected.

SELECT with DISTINCT on one columns

Same as above example DISTINCT apply only one columns.

Syntax

SELECT DISTINCT column_name1, column_name2, ... FROM table_name;

Example

SQL> SELECT * FROM userinfo;

  NO NAME                          ADDRESS
---- ----------------------------- ---------------------------
   1 Opal Kole                     63 street Ct.
   2 Max Miller                    41 NEW ROAD.
   3 Beccaa Moss                   2500 green city.
   4 Paul Singh                    1343 Prospect St
   5 Ken Myer                      137 Clay Road
   6 Jack Evans                    1365 Grove Way
   7 Opal Kole                     63 street Ct.
   8 Max Miller                    41 NEW ROAD.
   3 Beccaa Moss                   2500 green city.

9 rows selected.

SQL> SELECT DISTINCT name FROM userinfo;

NAME
----------------------------------
Opal Kole
Max Miller
Beccaa Moss
Paul Singh
Jack Evans
Ken Myer

6 rows selected.