How to Select Distinct Values from Specific Columns in SQL?

DISTINCT Selection for Specific Columns

While the DISTINCT keyword typically filters out entire rows with any duplicates, there may be instances where you require distinct values only for specific columns. Let's explore how to achieve this in the context of a query that retrieves data from a Products table.

The original query retrieves all columns from the Products table:

SELECT ID, Email, ProductName, ProductModel FROM Products

The objective is to modify the query to return unique Email addresses, allowing duplicates in other columns. To achieve this, we can utilize the ROW_NUMBER() function alongside a window partition by Email.

SELECT *
  FROM (
                SELECT  ID, 
                        Email, 
                        ProductName, 
                        ProductModel,
                        ROW_NUMBER() OVER(PARTITION BY Email ORDER BY ID DESC) rn
                    FROM Products
              ) a
WHERE rn = 1

Here's how this enhanced query operates:

1. The ROW_NUMBER() function assigns a unique row index, 'rn', within each partition of Emails in descending order of ID.
2. The subquery generates an intermediate table with all the columns from the original query, along with the row index 'rn'.
3. The outer query then filters the subquery's results to only include rows where 'rn' is equal to 1, effectively returning the most recent rows for each unique Email.

As a result, this modified query retrieves all columns from the Products table, ensuring that no duplicate Email addresses are present.

The above is the detailed content of How to Select Distinct Values from Specific Columns in SQL?. For more information, please follow other related articles on the PHP Chinese website!