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:
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:
Next, create a table with the following schema.
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:
'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:
This should return:
SQL Select Distinct Illustration
To select all the columns but eliminate the duplicate records, we can do:
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:
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:
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!!
