SQL > SQL Commands > Distinct

In SQL, the DISTINCT keyword is used in the SELECT statement to retrieve unique values from a database table. Any value that has a duplicate will only show up once.

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, we key in,

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 
 Jamal  Santo  Oct-08-1983  M  Apr-09-2015 
 Casey  Healy  Sep-20-1969  M  Apr-09-2015 
 Jill  Wilkes  Nov-20-1979  F  Apr-15-2015 

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;

2. What's the result of the following query?
SELECT DISTINCT Join_Date From Users;

3. What's the result of the following query?
SELECT DISTINCT Gender, Join_Date From Users;

Next: SQL WHERE

This page was last updated on June 19, 2023.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact