Selecting unique values from a column

sql

distinct

performance

best-practices

byAnton Shumikhin·Oct 17, 2024

To identify unique entries in a column, use the DISTINCT clause like this:

-- SQL statement regular as a clockwork:
SELECT DISTINCT column_name FROM table_name;

This command lists each distinct value in column_name, removing duplicates like a pro.

For a more practical scenario, if you have a transaction log and need to know the unique dates on which sales took place:

-- Time flies like an arrow; fruit flies like a banana.
SELECT DISTINCT Date FROM sales ORDER BY Date DESC;

Not only does this command pull unique dates, but it also organizes them in descending order because sometimes we all like to go back in time.

SQL tactics and performance tricks

Sorting unique values

Sorting the unique values can be accomplished by adding an ORDER BY after the DISTINCT clause:

-- SQL, now sorted for your pleasure:
SELECT DISTINCT column_name FROM table_name ORDER BY column_name;

Use ASC for ascending or DESC for descending order.

`GROUP BY` or `DISTINCT`

Remember that DISTINCT is your friendly neighbourhood uniquer, but if you're also aggregating data, GROUP BY should be your hero:

-- Just a hero by day, data aggregator by night:
SELECT column_name, COUNT(*) FROM your_table GROUP BY column_name;

This statement shows distinct values and counts occurrences because who doesn't like good data insights.

Making use of aliases

For more readability especially in intricate queries, AS can be your best friend to create aliases:

-- New aliases, who dis?
SELECT DISTINCT column_name AS UniqueValues FROM table_name;

With aliases, your value's purpose becomes clear as a summer day.

Efficiency is key

Check the table/column names. We all make mistakes.
Specify column names instead of wildcards (SELECT *) to optimize the performance.
Test your queries. Make sure they behave like well-trained pets.
Refer to official documentation for advanced performance tips.

Keeping your data in check

Matching data types

Ensure columns chosen for DISTINCT are compatible with it. Mismatched types can lead to wonky results.

Case sensitivity

SQL is usually case-insensitive. Ensure your database appreciates the difference between Flower and flower.

Dealing with NULL

DISTINCT treats NULL as a separate value. If NULLs are in your column, they will stand out in results.

Using subqueries

DISTINCT can be used in subqueries to make your larger query clean:

-- I like big data and I cannot lie:
SELECT * FROM table_name WHERE column_name IN (SELECT DISTINCT column_name FROM table_name);

This returns rows with unique values only.

explain-codes / Sql / Selecting unique values from a column

Linked

Order BY items must appear in the select list if SELECT DISTINCT is specified



Sql Query Distinct with Row_Number



Sql Group By with an Order By



How to use DISTINCT and ORDER BY in same SELECT statement?



How to use count and group by at the same select statement



How to select unique records by SQL



Sql COUNT* GROUP BY bigger than



SQL tactics and performance tricks Keeping your data in check