Sql Distinct | Distinct command in sql - sql tutorial - learn sql




 distinct command
  • DISTINCT command in SQL collects the unique or distinct records from a field of a table.
  • The SQL DISTINCT keyword is used in conjunction with the SELECT statement to eliminate all the duplicate records and fetching only unique records.
  • There may be a situation in which you have multiple duplicate records in a table.
  • When fetching such records, it makes more sense to fetch only those unique records instead of fetching duplicate records.
  • Any value that has a duplicate will only show up once.

SQL Distinct

SQL Distinct

Syntax

SELECT DISTINCT "column_name"
FROM "table_name";
  • "table_name" is the name of the table where data is stored, and "column_name" is the name of the column containing the data to be retrieved.

Examples

The examples will use the following table:

  • Table Store_Information
Store_Name Sales Txn_Date
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 300 Jan-08-1999
Boston 700 Jan-08-1999

Example 1: Use DISTINCT on one column

  • To select all distinct stores in Table Store_Information,
SELECT DISTINCT Store_Name FROM Store_Information;

Result:

Store_Name
Los Angeles
San Diego
Boston

Example 2: Use DISTINCT on multiple columns

  • We can apply DISTINCT to multiple columns. If we want to get a list showing all unique combinations of stores and transaction dates, we would type in the following,
SELECT DISTINCT Store_Name, Txn_Date FROM Store_Information

Result:

Store_Name Txn_Date
Los Angeles Jan-05-1999
San Diego Jan-07-1999
Los Angeles Jan-08-1999
Boston Jan-08-1999

Exercises

  • For these exercises, assume we have a table called Users with the following data

Table Users

First_Name Last_Name Birth_Date Gender Join_Date
Sophie Lee Jan-05-1960 F Apr-05-2015
Richard Brown Jan-07-1975 M Apr-05-2015
Casey Healy Sep-20-1969 M Apr-09-2015
Jamal Santo Oct-08-1983 M Apr-09-2015
Jill Wilkes Nov-20-1979 F Apr-15-2015

Interview questions in distinct command in SQL:

Example 1

1. Which of the following SQL statement is valid ?

a) SELECT DISTINCT * FROM Users; 
b) SELECT DISTINCT First_Name FROM Users; 
c) SELECT DISTINCT First_Name Last_Name FROM Users

Answer:

SELECT DISTINCT First_Name FROM Users; 

Example 2

2. What's the result of the following query ?

SELECT DISTINCT Join_Date From Users;

Answer

Join Date
Apr-05-2015
Apr-09-2015
Apr-15-2015

Example 3

3. What's the result of the following query ?

SELECT DISTINCT Gender, Join_Date From Users;

Answer

Gender Join_Date
F Apr-05-2015
M Apr-05-2015
M Apr-09-2015
F Apr-15-2015

SQL DISTINCT Sample Application

SQL DISTINCT Sample Application

SQL Distinct Example

SQL Distinct Example

Related Searches to SQL Distinct | Distinct command in sql - Sql tutorial