Select Distinct Rows in SQL

Learn How to Select Distinct Rows in SQL

Selecting distinct records in an SQL database through the SELECT DISTINCT statement is a helpful and frequently used database query. The SELECT DISTINCT statement returns only distinct values, which enables database administrators to get a quick overview of database records and generate insights from data.

Let’s learn how the SELECT DISTINCT statement works, and how to combine it with other frequently used statements such as COUNT or a WHERE statement. We will illustrate the SELECT DISTINCT statement with an easy-to-understand example.

SELECT DISTINCT Rows in SQL: Here’s How

Let’s design a database for global smartphone sales to understand better how selecting distinct records from a table can help us extract insights from our dataset.

Our database has five fields (and many more in reality):

• GUID, which serves as the table’s Primary Key. For the sake of simplicity, let’s use simple numbers as the primary key.
• Brand
• Manufacturer
• Model
• Month
• Units Sold

Here is what our database table would look like with five records in it:

Let’s say we want to find out which distinct brands are in this dataset.

By writing a SELECT DISTINCT statement, we can use SQL to return only distinct brands from the “Brand” column.

SELECT DISTINCT
  (`SmartphoneSale`.`Brand`)
FROM
  `SmartphoneSale`

Here’s what our query returns:

Samsung, Xiaomi, and Apple are the distinct brands in our dataset.

The order of our results seems rather random: we would like these to be ordered alphabetically, so let’s add a few more conditions to our SQL statement.

DISTINCT and ORDER BY

By adding an ORDER BY to our SQL statement, we can easily adjust the order of our results.

SELECT DISTINCT
  (`SmartphoneSale`.`Brand`)
FROM
  `SmartphoneSale`
ORDER BY
  (`SmartphoneSale`.`Brand`) ASC

Now our results look like this: the three brands are listed alphabetically. Much better!

Let’s take this one step further.

Let’s say we only want to see those brands that sold smartphones in January. If you look at our dataset, you will see that Xiaomi (mysteriously) did not sell any smartphones in January, but Apple and Samsung did.

Select Distinct: Adding a WHERE clause

To return the distinct brands that sold smartphones in January, we adjusted our query like so:

SELECT
  `SmartphoneSale`.`Month`,
  `SmartphoneSale`.`Brand`
FROM
  `SmartphoneSale`
WHERE
  (`SmartphoneSale`.`Month`="January")
ORDER BY
  `SmartphoneSale`.`Brand` ASC

Here’s what the query returns:

Perfect: the query says that only Apple and Samsung sold smartphones in January. By looking at the original dataset, we can confirm that this is true (this is a luxury you won’t have once you deal with larger datasets).

Working with SQL in Five

Five is a rapid application development environment that lets you create and query a MySQL database from scratch. In addition, you can use Five to build a responsive, login-protected web application on top of your database.

Five gives you point-and-click tools for creating forms, charts, data views, or PDF reports that visualize data stored in your SQL database. Five is the fastest way to go from database to web app.

To get started with Five, sign up for a free trial and follow one of our code-along tutorials.

Go From SQL Database to Web App
Start Developing