When working with databases, it's crucial to have an understanding of how to write efficient and accurate SQL queries. One such query is the SELECT DISTINCT statement, which is used to fetch unique values from a specific column. This guide will walk you through the proper usage and ordering of items in the SELECT DISTINCT list.
Table of Contents
- What is SELECT DISTINCT?
- Using SELECT DISTINCT with ORDER BY
- Examples of SELECT DISTINCT and ORDER BY
- FAQs
What is SELECT DISTINCT?
The SELECT DISTINCT statement is used to return only distinct (unique) values in the specified column. This is particularly useful when you want to eliminate duplicate values from your result set.
SELECT DISTINCT column_name
FROM table_name;
Using SELECT DISTINCT with ORDER BY
To sort the unique values returned by a SELECT DISTINCT query, you can use the ORDER BY clause. This clause allows you to order the result set based on one or more columns, either in ascending (ASC) or descending (DESC) order.
To use SELECT DISTINCT with ORDER BY, simply add the ORDER BY clause followed by the column(s) you want to sort the results by.
SELECT DISTINCT column_name1, column_name2
FROM table_name
ORDER BY column_name1 ASC, column_name2 DESC;
Examples of SELECT DISTINCT and ORDER BY
To better understand how to use SELECT DISTINCT with ORDER BY, let's take a look at some examples.
Example 1: Basic SELECT DISTINCT with ORDER BY
Suppose we have a table called employees with the following data:
| id | first_name | last_name | department |
|---|---|---|---|
| 1 | John | Doe | HR |
| 2 | Jane | Smith | IT |
| 3 | John | Doe | HR |
| 4 | Mary | Johnson | IT |
If we want to fetch a list of unique departments and sort them alphabetically, we can use the following query:
SELECT DISTINCT department
FROM employees
ORDER BY department ASC;
This query will return the following result:
| department |
|---|
| HR |
| IT |
Example 2: SELECT DISTINCT with Multiple Columns and ORDER BY
Let's say we want to fetch a list of unique employee names, including both first_name and last_name, and sort them by last_name in ascending order. We can use the following query:
SELECT DISTINCT first_name, last_name
FROM employees
ORDER BY last_name ASC;
This query will return the following result:
| first_name | last_name |
|---|---|
| John | Doe |
| Mary | Johnson |
| Jane | Smith |
FAQs
What is the difference between SELECT DISTINCT and GROUP BY?
While both SELECT DISTINCT and GROUP BY can be used to eliminate duplicates from your result set, they serve different purposes. SELECT DISTINCT is used to fetch unique values from a single column or combination of columns, while GROUP BY is used to group rows that have the same values in specified columns and perform aggregate functions like COUNT, SUM, AVG, etc.
Can I use SELECT DISTINCT with aggregate functions?
No, you cannot use SELECT DISTINCT with aggregate functions directly. If you want to perform an aggregate function on distinct values, you can use the DISTINCT keyword inside the aggregate function like this:
SELECT COUNT(DISTINCT column_name)
FROM table_name;
Can I use SELECT DISTINCT with JOIN?
Yes, you can use SELECT DISTINCT with JOIN statements. When using SELECT DISTINCT in a JOIN query, you can eliminate duplicate rows based on the specified columns from the joined tables.
Can I use SELECT DISTINCT with multiple columns?
Yes, you can use SELECT DISTINCT with multiple columns. When using SELECT DISTINCT with multiple columns, the query will return unique combinations of the specified columns.
Can I use SELECT DISTINCT with aliases?
Yes, you can use aliases with SELECT DISTINCT. In this case, you must use the alias in the ORDER BY clause as well. For example:
SELECT DISTINCT column_name1 AS alias1, column_name2 AS alias2
FROM table_name
ORDER BY alias1 ASC, alias2 DESC;