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.

Practice #1: Retrieve distinct rows for one column.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- 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

Next tutorial: Using String Functions, Part 1
Back to Section Index: Single Table SELECT Statement
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search