DISTINCT Keyword in Case of Multiple Columns

Although DISTINCT fetches the unique records from a table it may not always amount to be useful in certain scenarios when we have to find unique records from multiple columns. For example, consider the below scenario:-

We have an ‘orders‘ table which has the following columns: order_id, customer_id, product_id, order_date.

Query

CREATE TABLE orders (
         order_id INT PRIMARY KEY,
         customer_id INT,
         product_id INT,
         order_date DATE
);

INSERT INTO orders VALUES
    (1, 101, 1, '2023-01-10'),
    (2, 102, 2, '2023-01-15'),
    (3, 101, 1, '2023-01-20'),
    (4, 103, 2, '2023-02-01'),
    (5, 102, 3, '2023-02-05');

Consider the Below Problem

You’re required to fetch the order date of every product in the table along with the product’s ID. (Two columns)

See what happens when we apply the DISTINCT keyword here

Explanation

• DISTINCT clause is applied on two columns here – product_id and order_date
• Both columns when combined has unique value but individually, they have the same values which causes issue.

The DISTINCT clause doesn’t work here because we’re trying to fetch unique records from multiple columns. DISTINCT considers all the records to be unique because –

product id in 1st row has order_date = 2023-01-10 and product_id in 3rd row has order_date 2023-01-20. Both are unique when we combine both the columns but are individually different and this is not what we wanted to achiever. So, to resolve this issue we must make use of – the GROUP BY clause.

GROUP BY

It is used to group the records based on a column or set of columns and then perform operations. See the below query:-

SELECT product_id, order_date
FROM orders
GROUP BY product_id;

Output:

Explanation: Unique products are fetched along with their order_date.

MySQL is a relational database management system that can store data and we can query the stored data using SQL. SQL is a standard language to manipulate the database. The data fetching process can be applied with many filters as we might need only some specific data, or we might want to exclude some data then we can apply a filter using something called – ‘CLAUSE‘ in SQL. In this article, we’ll be discussing the ‘DISTINCT‘ clause.