CODEWITHSUNDEEP
sql
Oliver
Oliver

Reputation: 11597

SQL - 'DISTINCT' based on only some columns?

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

Upvotes: 44

Views: 131553

Answers (5)

dana
dana

Reputation: 18105

Assuming the address table has an id column:

select p.fname, p.lname, a.state, a.country
from person p
join address a on a.personid = p.personid
where not exists
    (select *
    from address a2
    where a2.personid = a.personid
      and a2.addressid < a.addressid)

My query returns all people with addresses. The exists() clause is used to determine that the returned address has the lowest addressid assigned to the person. The result will only contain 1 address per person.

EDIT: Another way to do this using top that has not been shown by others:

select p.fname, p.lname, a.state, a.country
from person p
join address a on a.addressid =
    (select top 1 a2.addressid
    from address a2
    where a2.personid = p.personid)

This should be very efficient as the nested query will short circuit on the first address found for each person.

Upvotes: 6

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

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

Upvotes: 13

Kerrek SB
Kerrek SB

Reputation: 476950

Try an aggregate:

SELECT user, address FROM users
JOIN addresses ON (users.user_id = addresses.user_id)
GROUP BY user;

Upvotes: 1

JNK
JNK

Reputation: 65147

SELECT Name, MAX(Address), MAX(other field)...
FROM MyTable
GROUP BY Name

Will give you one row per Name.

Upvotes: 9

Matthew
Matthew

Reputation: 10444

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

Upvotes: 17

Related Questions