duplicate record after using distinct-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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)