1

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?

1

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  |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.