SQL - 'DISTINCT' based on only some columns?

Question

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 

Answer 1

You probably need to use GROUP BY instead of DISTINCT in this case.

Post your query now and I will help you more.

Alternatively, if you just want to return the first address, that's a different query entirely. Does it need to return the address? What data do you need? What does "first" mean in this context? How is the data ordered?

Arbitrarily you could do something like this (untested), depending on your DB:

SELECT      userID     , FIRST(address) FROM     yourTable GROUP BY     userID 

Answer 2

If Addresses has an ID field:

(updated for SQL-Server)

SELECT      Users.Firstname,     Users.Surname,     Users.UserId,      Users.Recommendations,     Addresses.City,     Addresses.Region,     Addresses.Country FROM     Users INNER JOIN     Addresses ON Users.UserId = Addresses.UserId WHERE Addresses.ID =      ( SELECT TOP 1 A2.ID       FROM Addresses AS A2       WHERE Users.UserId = A2.UserId     ) ORDER BY     Users.Recommendations 

---

Using SQL-Server's window and ranking functions:

SELECT      Users.Firstname,     Users.Surname,     Users.UserId,      Users.Recommendations,     Addresses.City,     Addresses.Region,     Addresses.Country FROM     Users INNER JOIN      ( SELECT *             , ROW_NUMBER() OVER (PARTITION BY UserID) AS rn        FROM Addresses      ) AS Addresses ON Users.UserId = Addresses.UserId                     AND Addresses.rn = 1 ORDER BY     Users.Recommendations