Sql-server – How to remove duplicates values within particular ID

sql server

Im struggling with removing duplicate values within certain employee ID. Here is the situation:

ID ||  VALUE1   || VALUE2   || DATE

11 || Position1 || Other1   || 2019-04-04
11 || Position2 || Other3   || 2019-05-04
11 || Position1 || Other1   || 2019-06-04

12 || Position2 || Other3   || 2019-01-04
12 || Position1 || Other1   || 2019-03-04
12 || Position2 || Other3   || 2019-04-04
12 || Position3 || Other1   || 2019-05-04

13 || Position1 || Other3   || 2019-04-04
13 || Position3 || Other1   || 2019-05-04
13 || Position1 || Other3   || 2019-06-04

What I want to do is to remove repeating values for column VALUE1 but only within certain ID and the value which stays is the newest. So the results can look like:

ID ||  VALUE1   || VALUE2   || DATE

11 || Position2 || Other3   || 2019-05-04
11 || Position1 || Other1   || 2019-06-04

12 || Position1 || Other1   || 2019-03-04
12 || Position2 || Other3   || 2019-04-04
12 || Position3 || Other1   || 2019-05-04

13 || Position3 || Other1   || 2019-05-04
13 || Position1 || Other3   || 2019-06-04

I dont know how to achieve that using partition by or distinct. Any advices?

Best Answer

Perhaps something like this?

--demo setup
declare @table1 table (
  ID INTEGER,
  VALUE1 VARCHAR(9),
  VALUE2 VARCHAR(6),
  DATE VARCHAR(10)
);

INSERT INTO @table1
  (ID, VALUE1, VALUE2, DATE)
VALUES
  ('11', 'Position1', 'Other1', '2019-04-04'),
  ('11', 'Position2', 'Other3', '2019-05-04'),
  ('11', 'Position1', 'Other1', '2019-06-04'),
  ('12', 'Position2', 'Other3', '2019-01-04'),
  ('12', 'Position1', 'Other1', '2019-03-04'),
  ('12', 'Position2', 'Other3', '2019-04-04'),
  ('12', 'Position3', 'Other1', '2019-05-04'),
  ('13', 'Position1', 'Other3', '2019-04-04'),
  ('13', 'Position3', 'Other1', '2019-05-04'),
  ('13', 'Position1', 'Other3', '2019-06-04');

--solution
;with _cte as
(
select *,ROW_NUMBER() over (partition by id, value1 order by [date] desc) as rn from @table1
)
select * from _cte where rn=1

| ID | VALUE1    | VALUE2 | DATE       | rn |
|----|-----------|--------|------------|----|
| 11 | Position1 | Other1 | 2019-06-04 | 1  |
| 11 | Position2 | Other3 | 2019-05-04 | 1  |
| 12 | Position1 | Other1 | 2019-03-04 | 1  |
| 12 | Position2 | Other3 | 2019-04-04 | 1  |
| 12 | Position3 | Other1 | 2019-05-04 | 1  |
| 13 | Position1 | Other3 | 2019-06-04 | 1  |
| 13 | Position3 | Other1 | 2019-05-04 | 1  |
Related Question