SQL Standard

SQL Select Distinct

The distinct clause in SQL allows you to select unique elements. This, in turn, helps to remove duplicate records from a result set.

Let us learn how to use the DISTINCT clause in conjunction with the select statement to filter for unique records.

SQL DISTINCT Clause

The syntax for using the distinct clause is as shown below:

SELECT DISTINCT column1, column2,.....columnN

FROM table_name

WHERE [condition]

Although the where clause is optional, it allows you to be more precise in the records to fetch from the database.

Remember that the distinct clause will evaluate all the specified columns to determine the unique records.

For example, if a column contains null values, the distinct clause will select the first null record and remove all the others from the resulting set. Hence, when working with distinct, a NULL is treated as a value.

SQL Distinct Example

Let us illustrate how to use the distinct clause in SQL.

We can start by creating a sample database and table as shown in the queries below:

create database sampled;

Next, create a table with the following schema.

create table users(

id serial primary key,

first_name varchar(100),

last_name varchar(100),

state varchar(25),

active bool

);

Once we have the table schema created, we can add sample data as shown:

insert into users(first_name, last_name, state, active) values (

'Mary', 'Smith', 'New York', TRUE);

insert into users(first_name, last_name, state, active) values (

'Linda', 'Williams', 'Colorado', FALSE);

insert into users(first_name, last_name, state, active) values (

'Taylor', 'Moore', 'Utah', TRUE);

insert into users(first_name, last_name, state, active) values (

'Susan', 'Wilson', 'Washington', TRUE);

insert into users(first_name, last_name, state, active) values (

'Mary', 'Smith', 'New York', TRUE);

insert into users(first_name, last_name, state, active) values (

'Taylor', 'Moore', 'Utah', TRUE);

The above queries add sample data to the users table. Note that the data contain duplicate values.

We can query the table as shown:

select * from users;

This should return:

SQL Select Distinct Illustration

To select all the columns but eliminate the duplicate records, we can do:

select distinct first_name, last_name, state, active from users;

The above query should filter all the duplicate records and only return the unique ones. An example output is as shown:

To select distinct on a specific column, you can use the distinct on clause. For example, to select distinct records using the first_name column, we can do:

select distinct on (first_name) first_name, last_name, state, active from users;

The above query filters for the unique records using the first_name column.

If you want to calculate the number of distinct records in a result set, you can use the distinct clause with the count function. An example is as shown:

select count(distinct first_name) from users;

The code above should return the number of unique records using the first_name column. An example output is as shown:

Closing

The tutorial above discussed the distinct clause in SQL and how to use it to filter for unique records within a table.

Thanks for reading!!

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list