How do you select a single record for duplicate rows in SQL?

 To select a single record for duplicate rows in SQL, you typically need to identify the criteria that define "duplicates." Usually, duplicates are rows that have identical values in all or certain columns, and you want to choose one of them to keep.

Here’s how you can do it using different SQL techniques:

1. Using ROW_NUMBER() (Window Function)

One of the most common and flexible approaches is to use the ROW_NUMBER() window function. It assigns a unique number to each row within a partition of your data, ordered by a specified column(s). You can then filter out only the first row (or any specific row) in each duplicate group.

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY some_column) AS rn
    FROM your_table
)
SELECT *
FROM cte
WHERE rn = 1;

• PARTITION BY column1, column2: Specifies the columns that define a "duplicate" (adjust this based on your use case).
• ORDER BY some_column: Determines which row is selected if there are multiple duplicates (e.g., choose the first or last based on the date, ID, etc.).
• WHERE rn = 1: Filters out only the first row for each group of duplicates.

2. Using GROUP BY and MIN or MAX

If you just want to keep one record per duplicate group and you don’t need any specific order, you can use GROUP BY with an aggregate function such as MIN() or MAX().

SELECT column1, column2, MIN(some_column) AS some_column
FROM your_table
GROUP BY column1, column2;

• GROUP BY column1, column2: Groups rows that have the same values in column1 and column2.
• MIN(some_column): Returns the smallest value of some_column for each group (you can also use MAX() or other aggregate functions).

3. Using DISTINCT

If you're selecting columns and just want to remove exact duplicates (i.e., rows with identical values in every column you're selecting), you can use DISTINCT to filter out duplicates.

SELECT DISTINCT column1, column2, some_column
FROM your_table;

• This will return only distinct combinations of column1, column2, and some_column.

4. Using JOIN with a Subquery (if you want to exclude duplicates)

Sometimes, you might want to exclude duplicates from your results entirely. For this, you can join the table with a subquery that selects only one of the duplicates.

SELECT t.*
FROM your_table t
JOIN (
    SELECT MIN(id) AS id
    FROM your_table
    GROUP BY column1, column2
) AS subquery ON t.id = subquery.id;

• This query selects the record with the smallest id for each duplicate group defined by column1 and column2.

5. Using DISTINCT ON (PostgreSQL specific)

In PostgreSQL, you can use the DISTINCT ON syntax to return only the first row of each set of duplicates.

SELECT DISTINCT ON (column1, column2) column1, column2, some_column
FROM your_table
ORDER BY column1, column2, some_column;

• DISTINCT ON (column1, column2): Ensures you get one row for each combination of column1 and column2.
• ORDER BY column1, column2, some_column: Determines which row to keep for each group (the first row as ordered by some_column).

Conclusion

The best method depends on your specific requirements:

• ROW_NUMBER() is flexible and works well when you need to choose a specific row (e.g., the first or last).
• GROUP BY with aggregates is simple and works well for basic de-duplication.
• DISTINCT is the easiest when you simply need to remove exact duplicates.
• DISTINCT ON (in PostgreSQL) is a concise option for picking one record from duplicate groups.

If you're unsure which to choose, consider what defines the duplicates and how you want to decide which one to keep.