Sal select statement to filter results

Question

I have joined together tables but the data has some duplicates by design. To simple the example below

User address

User1 100 main st.

User1 999 everyone St.

User2 400 elm st.

User2 999 everyone St.

User3 300 pine st.

User3 999 everyone St.

User4 999 everyone St.

How would I get every user only once and the one address getting the preferred address (instead of the generic 999 everyone st. that everyone gets).

User1 100 main st.

User2 400 elm st.

User3 300 pine st.

User4 999 everyone St.

Answer 1

As I am unsure of how your tables are built, the answer might the a little too general, but I am pretty sure that selecting your values distinctively might fix your problem.

You would do it like so:

SELECT DISTINCT USER.ID, STREET.STREET_NUMBER
FROM USER
  INNER JOIN --the rest of your code...

That way, when you use distinct, it will only display the next row if the id of the user is different. Effectively, that will only display the first address row of each user, which is the actual value that you want to have, instead of displaying both the default row and the new one.

Answer 2

You can use row number with Partition by like this

Select userId, address from (
Select userId , address, row_number() over(partition by userId order by address) rw
From your_join_tables) t
Where t.rw=1