Extracting Unique Values Using The DISTINCT Keyword

When it comes to refining and simplifying queries, the DISTINCT keyword proves to be invaluable. In this blog post, we’ll dive into the intricacies of the SQL DISTINCT command, explore its syntax, and showcase its utility through a variety of use cases.

There are many ways to build SQL competency so we have decided to take a different approach. For the next few days, you will be serving as the newly hired Data Analyst for Basira and Co, An Afro fusion restaurant with a network of chains across Africa and you’ll be reporting to the Chief Growth Officer (CGO) Mrs Ahmadu.

Everyday, you’ll get analytical requests/tasks from her related to the SQL topic of the day. The goal for this is to make each lesson as practical and as relatable as possible.

HOW TO PRACTICE: Here is a link to our practice guide. We have already prepared the table schema in advance so all you need to do is plug that into DBFiddle.com and you are all set! The LinkedIn forum is a great way to share any comments or questions about the practice guide.

YOU’VE GOT MAIL!

Good Morning!
I had a conversation with our CEO and he’d like to revamp the dishes in our restaurant menu. Can you pull the number of unique dishes we serve per country?
Best Regards,
Mrs Ahmadu (CGO)

SQL TOPIC OF THE DAY

The DISTINCT keyword is used to retrieve unique values from a specified column or set of columns in a SQL query. It eliminates duplicate records, ensuring that only distinct values are returned. This is particularly useful when working with datasets where duplicate entries may exist, and you want to focus on unique information.
The basic syntax of using the DISTINCT keyword in a SQL query is as follows:

SELECT DISTINCT column1, column2, …
FROM table;

column1, column2: are the columns from which you want to retrieve distinct values.
table1: is the table containing the data.

Common Use Cases for the DISTINCT keyword

1. Retrieving Unique Values

Consider a table named colors that stores information about the colors used in a product catalog. To retrieve a list of unique product_colors from the table, you can use the following query:

SELECT DISTINCT color
FROM product_colors;

This query returns a list of distinct colors present in the color column of the product_colors table.

2. Eliminating Duplicate Entries

In scenarios where you have a table with redundant information, the DISTINCT keyword can help eliminate duplicate entries. Suppose you have a students table with columns like student_id and course_id. To find a list of unique courses that students are enrolled in, you can use:

SELECT DISTINCT course_id
FROM students;

This query ensures that each course appears only once in the result set, even if multiple students are enrolled in the same course.

3. Simplifying Aggregated Queries

When working with aggregate functions, using DISTINCT can simplify queries and provide clearer insights. For instance, consider a sales table with columns like product_id and quantity_sold. To find the number of unique products sold, you can use:

SELECT COUNT(DISTINCT product_id) as unique_products_sold
FROM sales;

This query counts the number of distinct product_id values, giving you the count of unique products sold.

SQL PRACTICE

Now, let’s write a script to answer the question of the day.
Mrs Ahmadu would like you to provide the number of unique dishes we serve per country

SELECT r.country, COUNT(o.dish_names) as no_of_restaurants
FROM restaurants r
LEFT JOIN orders o ON r.id = o.restaurant_id
GROUP BY country;

This helps us to identify the unique dishes served per country.

FEEDBACK FROM THE CGO

Thanks for that!
Can you also provide the number of unique dishes per restaurant and rank them?
Warm regards,
Mrs Ahmadu (CGO

With what we learned today, think you can solve this part on your own?
Did you learn anything new? Share your new insights with us on Facebook, LinkedIn or Instagram and don’t forget to tag us!