-1

What is the best SQL sentence to avoid repetitions on a table based on two fields, but list the rest of fields too? (in sql server) I would like some similar to:

SELECT DISTINCT[YEAR, USER_ID], OTHER_USER_ID, ETC 
FROM USERS
YEAR USER_ID OTHER_USER_ID
2020 ID_1 ID_256
2020 ID_1 ID_987
2020 ID_2 ID_357
2020 ID_2 ID_159
2021 ID_1 ID_456

Desired result:

YEAR USER_ID OTHER_USER_ID
2020 ID_1 ID_256
2020 ID_2 ID_357
2021 ID_1 ID_456

Thanks a lot!

user3682831
  • 45
  • 1
  • 1
  • 5

1 Answers1

0

Your data is not particularly suited for the desired output, having an "ID" value as a string including "ID_" is a bit of a design flaw, does the user_Id column include values that are not user Ids?

You can accomplish what you need with a simple row number filter, but it relies on ordering of strings which is far from ideal, you really need an additional integer to indicate the correct ordering, the result will ultimately be ambiguous with the data set provided.

Also, you should avoid using keywords (year) as column names where possible!

select year, user_id, other_user_id from (
    select year, user_id, other_user_id, row_number() over (partition by year, user_id order by year, user_id) rn
    from t
)x
where rn=1
Stu
  • 30,392
  • 6
  • 14
  • 33