duplicate record after using distinct

Question

I have this query

select distinct a.addressID,a.StreetNumber,a.Street,a.StreetType,a.PostalCode
from Address a
inner join UserAdress ca on a.addressID = ca.addressID

addressID is different in all cases, if i remove addressID and query the table, i get unique records. but if i keep addressID and query it, i get duplicate records. I see the userAddress table has 2 entries, i want to get the 1 ID from that so i can get unique address

please guide what is going here, how can i rewrite a join to make the query to fetch one record and then map it to the address table

don't want to use rownum because that is not giving right results

Impossible. `SELECT DISTINCT` only returns *distinct* rows. If there are 2 rows that *look* the same, they are different in some way; maybe one value has a link break at the end for example. Sample data and expected results will help us help you. — Thom A, Nov 22 '21 at 13:41
as i said, it has the different addressID, so i need to fetch one instead of both — Notion, Nov 22 '21 at 13:53
Then the rows aren't duplicates if they are different, @Notion . But again, Sample data and expected result will help us help you. — Thom A, Nov 22 '21 at 14:38
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Nov 22 '21 at 14:46

score 0 · Answer 1 · answered Nov 22 '21 at 13:59

If addressID is a number and you can use the group by like this with MAX or MIN



    select Max(a.addressID) as addressID, a.StreetNumber,a.Street,a.StreetType,a.PostalCode
    from  Address 
    group by a.StreetNumber,a.Street,a.StreetType,a.PostalCode

score 0 · Answer 2 · answered Nov 22 '21 at 14:04

If you wold like to get only one address, you can use Min() and Max() function with OVER() clause:

select distinct Max(a.addressID) OVER(ORDER BY a.addressID) AS addressID,
    a.StreetNumber,
    a.Street,
    a.StreetType,
    a.PostalCode
from Address a 
    inner join UserAdress ca on a.addressID = ca.addressID

For further details, please see: MIN(T-SQL)

duplicate record after using distinct

2 Answers2