CODEWITHSUNDEEP
sql-serverdistinct
Peter S
Peter S

Reputation: 575

Filter out duplicate row based on values in 2 columns

I have the following table:

CREATE TABLE [TABLE_1] 
(
    ID decimal(28,6) NULL,
    S_DATE datetime  NULL,
    NR decimal(28,6) NULL,
);

INSERT INTO TABLE_1 (ID, S_DATE, NR)
VALUES (1, '2020-01-01', 1),
       (1, '2020-01-01', 2),
       (1, '2020-05-02', 3);

A simple query:

SELECT *  
FROM Table_1 t1

Returns:

+----+----------------------+----+
| ID |        S_DATE        | NR |
+----+----------------------+----+
|  1 | 2020-01-01T00:00:00Z |  1 |
|  1 | 2020-01-01T00:00:00Z |  2 |
|  1 | 2020-05-02T00:00:00Z |  3 |
+----+----------------------+----+

But I want to filter out duplicates that share the same ID and S_DATE, like this:

+----+----------------------+----+
| ID |        S_DATE        | NR |
+----+----------------------+----+
|  1 | 2020-01-01T00:00:00Z |  1 |
|  1 | 2020-05-02T00:00:00Z |  3 |
+----+----------------------+----+

SQL FIDDLE: LINK

Upvotes: 0

Views: 233

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280429

You can use a common table expression which applies a row number to each subsequent row with the same ID/S_DATE values, then filter on that row number.

;WITH src AS 
(
  SELECT ID, S_DATE, NR, rn = ROW_NUMBER() OVER 
     (PARTITION BY ID, S_DATE ORDER BY NR)
  FROM dbo.TABLE_1
)
SELECT ID, S_DATE, NR
  FROM src
  WHERE rn = 1;

You could also do this, which is more straightforward, but it is much more complicated to do when you have many columns to group by (or if you want more complicated logic for which NR to include):

SELECT ID, S_DATE, NR = MIN(NR)
  FROM dbo.TABLE_1
  GROUP BY ID, S_DATE;

Fiddle

Upvotes: 1

Related Questions