When working with SQL Server, there are scenarios where we might need to retrieve unique combinations of values from multiple columns. This is where the SELECT DISTINCT statement comes in handy. It allows us to eliminate duplicate rows from the result set.
However, using SELECT DISTINCT it on multiple columns requires a slightly different approach compared to using it on a single column. In this article, we’ll explore how to use SELECT DISTINCT on multiple columns in SQL Server by understanding various examples and so on.
What is the DISTINCT Keyword?
The DISTINCT keyword is used in SQL queries to filter out duplicate rows in the result set. When the Distinct operator is used with a single column, it makes sure that columns only contain unique values, however, in scenarios where uniqueness is defined by combinations of multiple columns, a more complex method is needed.
The syntax for Selecting Distinct Rows on Multiple Columns:
To select distinct rows based on multiple columns in SQL Server, we need to specify all the columns whose combinations should be unique.
The syntax for such a query is defined below:
SELECT DISTINCT column1, column2, ...
FROM table_name;
Let’s set up an environment
Let’s create an example table called products and insert some data into it including duplicate values. Below are the examples of using SELECT DISTINCT on multiple columns in SQL Server with the provided products table:
Create Table:
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
unit_price DECIMAL(10, 2),
stock_quantity INT
);
Insert Data:
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00, 50),
(2, 'Smartphone', 'Electronics', 800.00, 100),
(3, 'Coffee Maker', 'Appliances', 50.00, 30),
(4, 'Backpack', 'Fashion', 40.00, 80),
(5, 'Desk Chair', 'Furniture', 150.00, 20),
(6, 'Laptop', 'Electronics', 1200.00, 50),
(7, 'Coffee Maker', 'Appliances', 50.00, 30);
Output:
Products Table
Examples of How to SELECT DISTINCT on multiple columns in SQL Server
Example 1: Select the Unique Combinations of product_name and category.
SELECT DISTINCT product_name, category
FROM products;
Output:
Unique Combinations of product_name and category
Explanation:
- SELECT DISTINCT: It tells the database to give only the unique values from the specified columns in the result set and not the repeated ones. Redundancy rows are cleaned up to make sure that each row in the output is unique.
- product_name, category: The query requests distinct combinations of values from these columns.
- FROM products: Specifies the table from which the data will be retrieved.
Example 2: Select the Unique Combinations of category and unit_price.
SELECT DISTINCT category, unit_price
FROM products;
Output:
Unique Combinations of category and unit_price
Explanation:
- SELECT DISTINCT: It tells the database to give only the unique values from the specified columns in the result set and not the repeated ones. Redundancy rows are cleaned up to make sure that each row in the output is unique.
- category, unit_price: The query requests distinct combinations of values from these columns.
- FROM products: Specifies the table from which the data will be retrieved. This query will return distinct combinations of category and unit_price from the products table.
Example 3: Select the Unique Combinations of category, unit_price, and stock_quantity.
SELECT DISTINCT category, unit_price, stock_quantity
FROM products;
Output:
Unique Combinations of category, unit_price, and stock_quantity
Explanation:
- SELECT DISTINCT: It tells the database to give only the unique values from the specified columns in the result set and not the repeated ones. Redundancy rows are cleaned up to make sure that each row in the output is unique.
- category, unit_price, stock_quantity: The query requests distinct combinations of values from these columns.
- FROM products: Specifies the table from which the data will be retrieved.
Conclusion
Overall, Using SELECT DISTINCT on multiple columns in SQL Server allows you to retrieve unique combinations of values from those columns. It’s a powerful tool for eliminating duplicate rows from your result set and ensuring that you only get the data you need. By understanding how to use SELECT DISTINCT on multiple columns, you can enhance the efficiency and accuracy of your SQL queries in SQL Server.