Alternative Approaches to SELECT DISTINCT on a Single Column in SQL Server T-SQL

SELECT DISTINCT operates on all the columns you specify in your query. It removes duplicate rows based on the combination of values in those columns.

However, there are alternative approaches to achieve similar results:

1. Using a Common Table Expression (CTE) with ROW_NUMBER():

This method involves creating a CTE that assigns a row number to each record within groups defined by the column you want unique values for. You can then filter the CTE to only include rows with a row number of 1 (the first occurrence of each distinct value).

Here's an example:

SELECT *
FROM (
  SELECT ID, SKU, Product, ROW_NUMBER() OVER (PARTITION BY Product ORDER BY ID) AS RowNumber
  FROM MyTable
) AS RankedProducts
WHERE RankedProducts.RowNumber = 1;

This query assigns a row number to each record based on the Product column. Then, it selects only the rows where RowNumber is 1, effectively giving you distinct Product values with all other columns included.

1. Using SET logic (for specific scenarios):

For specific cases, you can utilize SET logic to achieve distinct values. This might involve creating temporary variables and manipulating data within the T-SQL code block. However, this approach can be less readable and maintainable compared to the CTE method.

Using a Common Table Expression (CTE) with ROW_NUMBER():

-- Sample table
CREATE TABLE Products (
  ID INT PRIMARY KEY,
  SKU VARCHAR(20),
  Product NVARCHAR(50)
);

-- Insert some sample data
INSERT INTO Products (ID, SKU, Product)
VALUES (1, 'ABC123', 'Shirt'),
       (2, 'DEF456', 'Shirt'),
       (3, 'GHI789', 'Hat'),
       (4, 'ABC123', 'Shirt');

-- Query to get distinct Products with all columns
SELECT *
FROM (
  SELECT ID, SKU, Product, ROW_NUMBER() OVER (PARTITION BY Product ORDER BY ID) AS RowNumber
  FROM Products
) AS RankedProducts
WHERE RankedProducts.RowNumber = 1;

This code creates a sample table Products and inserts some data. The query then uses a CTE named RankedProducts to assign a row number based on the Product column. Finally, it selects only rows where RowNumber is 1, resulting in distinct Product values with corresponding ID and SKU.

Using SET logic (for specific scenarios):

Note: This approach is less recommended for most cases due to readability concerns.

-- Sample table (same as previous example)
CREATE TABLE Products (
  ID INT PRIMARY KEY,
  SKU VARCHAR(20),
  Product NVARCHAR(50)
);

-- Insert some sample data (same as previous example)
INSERT INTO Products (ID, SKU, Product)
VALUES (1, 'ABC123', 'Shirt'),
       (2, 'DEF456', 'Shirt'),
       (3, 'GHI789', 'Hat'),
       (4, 'ABC123', 'Shirt');

-- Variable to store processed products
DECLARE @ProcessedProducts NVARCHAR(MAX) = '';

-- Loop through each product
SELECT SKU, Product
FROM Products
WHERE @ProcessedProducts NOT LIKE '%' + Product + '%'
ORDER BY Product;

-- Update processed products variable
SET @ProcessedProducts = @ProcessedProducts + ',' + Product;

This code demonstrates a SET logic approach. It uses a variable @ProcessedProducts to store encountered products. The query loops through each product and checks if it already exists in the variable. If not, it adds the product details to the result set and the variable.

Using EXISTS (Limited applicability):

This method is suitable when you only need to select a specific column and don't require all columns in the result set. It utilizes a subquery with EXISTS to check for duplicate values based on the chosen column.

Here's an example:

SELECT Product
FROM Products
WHERE NOT EXISTS (
  SELECT 1 FROM Products AS p2
  WHERE p2.Product = Products.Product AND p2.ID <> Products.ID
);

This query checks for each product if there exists another record with the same Product value but a different ID. If no duplicate exists (NOT EXISTS), the current product is included in the result set.

Important Note: This approach might not be ideal for large datasets due to potential performance implications of subqueries.

Using UNION ALL (For specific use cases):

This method involves creating separate queries that select distinct values for the desired column and then combining them using UNION ALL. However, it's essential to ensure the order of the columns in both queries matches exactly.

SELECT DISTINCT Product
FROM Products

UNION ALL

SELECT DISTINCT SKU
FROM Products;

This query retrieves distinct values from both the Product and SKU columns and combines them using UNION ALL. Remember, this approach only works if you need distinct values from multiple columns and want them all in the result set.