How to Eliminate Duplicate Rows in SQL SELECT Statement for MySQL

Eliminating Duplicate Rows

When the result set from a SELECT statement contains duplicate rows, you may want to remove them and keep every row data to be unique for a column or combination of columns. You can use the DISTINCT or DISTINCTROW identifier to eliminate duplicate records. DISTINCT and DISTINCTROW are synonyms and specify removal of duplicate rows from the result set.

Read Prerequisites for this tutorial and practices if you haven't done so.

Consider the following facts when using DISTINCT or DISTINCTROW identifier in a SELECT statement:

In a SELECT statement, include DISTINCT or DISTINCTROW keyword after the SELECT clause.
Multiple columns can be specified after DISTINCT or DISTINCTROW keyword. In this case, the result set contains distinct combination of data from these columns.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

Practice #1: Retrieve distinct rows for one column.

-- Display distinct Quantity values
-- by using DISTINCT keyword
SELECT DISTINCT Quantity
FROM order_details
ORDER BY Quantity;
 
-- Display distinct Quantity values
-- by using DISTINCTROW keyword
SELECT DISTINCTROW Quantity
FROM order_details
ORDER BY Quantity;

The query above retrieves distinct quantity data from order_details table.

There are total 55 rows retrieved out of 2,155 rows in order_details table.

Query result set:
Distinct quantity data from order_details table

Practice #2: Retrieve distinct rows for the combination of two columns.

-- Retrieve distinct rows based on 
-- two columns (Quantity and Discount)
SELECT DISTINCT Quantity, Discount
FROM order_details
ORDER by Quantity, Discount;

There are total 238 rows retrieved out of 2,155 rows in order_details table.

Query result set:
Retrieve distinct data for two columns in order_details table

Practice #3: Retrieve distinct rows for the combination of three columns.

-- Retrieve distinct rows based on three 
-- columns (UnitPrice, Quantity, and Discount)
SELECT DISTINCT UnitPrice, Quantity, Discount
FROM order_details
ORDER by UnitPrice, Quantity, Discount;

There are total 1,711 rows retrieved out of 2,155 rows in order_details table.

Query result set:
Retrieve distinct data for three columns in order_details table

Other tutorials in this category

1. The Basic SELECT Statement

2. Using Column Alias in SELECT Statement

3. Using Literal Character Strings

4. Perform Arithmetic Operations

5. Use WHERE Clause to Conditionally Select Rows

6. Sorting Data

7. Using LIMIT Clause to Constrain the Number of Rows Retrieved

8. Using Comparison Operators, Part I

9. Using Comparison Operators, Part II

10. Using LIKE Comparison Operator

11. Using Logical Operators

Back to Tutorial Index Page

	Custom Search