SQL Select Distinct Data

In this article, we will explore the different ways to extract distinct data from a single table in SQL. We will use an example scenario to illustrate the process and provide step-by-step instructions.

Introduction

When working with large datasets, it’s essential to extract only the necessary information. In many cases, you might want to select distinct values from one or more columns and join them with other columns to create a new dataset. However, SQL doesn’t natively support this type of operation using simple SELECT statements.

To achieve this, we’ll use some advanced techniques such as subqueries, joins, and aggregation functions like MIN and MAX. We’ll also explore how to handle duplicates and edge cases.

The Challenge

Let’s dive into the example scenario provided by Stack Overflow user. They have a single table named table1 with four columns: id, animal, color, and name, breed etc.. They want to extract distinct data for two columns: animal and color. However, they also want to include extra information from other columns like id and size.

The desired output would be a table with the following structure:

The Solution

The answer provided by the Stack Overflow user uses a combination of subqueries and aggregation functions to achieve this. Here’s the step-by-step solution:

Step 1: Create a Derived Table (CTE)

WITH test (id, animal, color, c_size) AS (
  -- Insert data from multiple rows into a single row
  (SELECT 1, 'cat', 'white', 'medium' FROM DUAL UNION
   SELECT 2, 'cat', 'white', 'big'    FROM DUAL UNION
   SELECT 3, 'dog', 'white', 'medium' FROM DUAL UNION
   SELECT 5, 'dog', 'white', 'big'    FROM DUAL UNION
   SELECT 6, 'cat', 'black', 'small'  FROM DUAL UNION
   SELECT 7, 'cat', 'black', 'small'  FROM DUAL)
)

In this step, we create a Common Table Expression (CTE) named test with the same structure as our original table. We use the UNION ALL operator to combine multiple rows into a single row.

Step 2: Select Distinct Data

SELECT MIN(id) id, animal, color, MAX(c_size) c_size
FROM test
GROUP BY animal, color;

In this step, we select only the distinct combinations of animal and color. We use the MIN function to get the smallest id value for each combination and the MAX function to get the largest c_size value.

The FROM test clause specifies that we want to use the test CTE as our temporary table. The GROUP BY animal, color; clause groups the data by these two columns, allowing us to count distinct values.

Explanation

Let’s break down what happens in this solution:

1. We create a CTE named test, which is essentially a temporary table that we can use within our SQL query.
2. Inside the CTE, we insert multiple rows into a single row using the UNION ALL operator. This allows us to combine different data into one set of values.
3. In the main query, we select only distinct combinations of animal and color. By grouping by these two columns, we count how many times each combination appears in our original table.
4. To get the smallest id value for each group, we use the MIN(id) function.
5. To get the largest c_size value for each group, we use the MAX(c_size) function.

Alternative Solutions

There are other ways to achieve this result using different techniques:

Using DISTINCT

SELECT DISTINCT id, animal, color, size
FROM table1;

However, this approach won’t give us the desired output because we need to select distinct combinations of animal and color, not just individual columns.

Using GROUP BY with a subquery

SELECT t1.id, t1.animal, t1.color, t2.size
FROM table1 t1
JOIN (
  SELECT animal, color, MAX(size) as size
  FROM table1
  GROUP BY animal, color
) t2 ON t1.animal = t2.animal AND t1.color = t2.color;

This approach joins the original table with a subquery that groups by animal and color, then selects the maximum size value. However, this will also give us duplicate rows if there are multiple id values for the same combination of animal and color.

Using window functions

SELECT id, animal, color, size,
       ROW_NUMBER() OVER (PARTITION BY animal, color ORDER BY id) as row_num
FROM table1;

This approach uses a window function to assign a unique row number to each combination of animal and color. We can then select only the rows with the smallest id value for each group using ROW_NUMBER().

Conclusion:

In this article, we explored different ways to extract distinct data from a single table in SQL. We used advanced techniques like subqueries, joins, and aggregation functions like MIN and MAX to achieve this. We also discussed alternative solutions and how to handle duplicates and edge cases. By understanding these concepts, you’ll be able to tackle similar challenges in your own SQL development projects.

Additional Examples

Here’s an example that shows the difference between using UNION ALL and multiple INSERT INTO ... VALUES statements:

-- Using UNION ALL
SELECT 1 AS id, 'cat' AS animal, 'white' AS color, 'medium' AS size FROM DUAL;
SELECT 2 AS id, 'cat' AS animal, 'black' AS color, 'small' AS size FROM DUAL;
SELECT 3 AS id, 'dog' AS animal, 'white' AS color, 'big' AS size FROM DUAL;

-- Using multiple INSERT INTO ... VALUES statements
INSERT INTO test (id, animal, color) VALUES (1, 'cat', 'white');
INSERT INTO test (id, animal, color) VALUES (2, 'cat', 'black');
INSERT INTO test (id, animal, color) VALUES (3, 'dog', 'white');

And here’s an example that shows how to use ROW_NUMBER() with a window function:

SELECT id, animal, color, size,
       ROW_NUMBER() OVER (PARTITION BY animal, color ORDER BY id) as row_num
FROM table1;

In this case, we’ll get the following result:

Note that the row_num column assigns a unique row number to each combination of animal and color, based on the smallest id value.

Last modified on 2025-03-05