When working with SQL Server, the issue of duplicate records is a common problem that many developers face. In this blog post, we will discuss a solution to the question of how to display only unique records from a table.

Typically, the first approach that comes to mind is to use the DISTINCT or GROUP BY clauses. However, these solutions may not always be applicable in all scenarios. Let’s consider an example where a user wants to display only the latest records based on a specific criteria, such as the highest price value.

Let’s take a look at the following example to better understand the problem:

CREATE TABLE Table1 (
    ID INT,
    Product VARCHAR(100),
    Price INT,
    Color VARCHAR(100)
)

INSERT INTO Table1
SELECT 1, 'Toy', 100, 'Black'
UNION ALL
SELECT 2, 'Pen', 100, 'Black'
UNION ALL
SELECT 3, 'Pencil', 100, 'Blue'
UNION ALL
SELECT 4, 'Pencil', 100, 'Red'
UNION ALL
SELECT 5, 'Pencil', 200, 'Yellow'
UNION ALL
SELECT 6, 'Cup', 300, 'Orange'
UNION ALL
SELECT 7, 'Cup', 400, 'Brown'

SELECT Product, Price, Color
FROM Table1

SELECT Product, Price, Color
FROM (
    SELECT Table1.*, ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Price DESC) AS RN
    FROM Table1
) AS t
WHERE RN = 1
ORDER BY ID

DROP TABLE Table1

In this example, the user wants to display only the unique records based on the highest price value for each product. To achieve this, we can use the ROW_NUMBER function along with the PARTITION BY clause to partition the data by the product column and order it by the price column in descending order. Then, we select only the records with a row number of 1, which represents the highest price for each product.

By using this approach, we can effectively display only the unique records based on the specified criteria.

Feel free to share your own solutions in the comments below. I will be happy to include them in a future blog post with due credit.