SQL - 'DISTINCT' based on only some columns?

Viewed 100639

I have a database with two tables. One of the tables contains users, the other contains addresses for those users. Each user may have several addresses (though each address is tied to only one user.)

I want to create a search that only returns one entry for each user, even if that user has several addresses. It doesn't matter which address the search pulls back - whatever the search finds first is enough.

Here is an example search result:

tst  olix  Chicago  IL  USA
tst  olix  Los Angeles  CA  USA
tst2 olix2 Houston  TX USA

I need the search to be such that it only returns 2 rows, rather than 3.

Any ideas?

SELECT DISTINCT
    Users.Firstname, Users.Surname, Users.UserId, 
    Users.Recommendations, Addresses.City, Addresses.Region,
    Addresses.Country
FROM
    Users INNER JOIN
    Addresses ON FT_TBL.UserId = Addresses.UserId
ORDER BY
    Users.Recommendations
5 Answers
Related