How to select first row from a join that returns multple rows on the primary key

Question

This is related to this question: Joining multiple tables results in duplicate rows

I have two tables that I am joining. They share a key. The person table has one name per primary key but the email table has multiple emails per personId. I want to only show the first email per person. Presently I get multiple rows per person because they have multiple emails. I am running SQL-Server 2005.

EDIT: This is T-SQL. First email is literally the first email row per person.

Edit 2: First email as I see it would be the first email row that shows up in the join as SQL works through the query. I does not matter which email shows up. Only that no more than one email shows up. I hope that makes it clearer.

Table1: Person
Table2: Email

Select Person.PersonName, Email.Email
From person 
left join on Person.ID=Email.PersonId;

Answer 1

SELECT
    A.PersonName, A.Email
FROM
        (
        Select Person.PersonName, Email.Email
            ,ROW_NUMBER() OVER(PARTITION BY Person.ID ORDER BY Email.Email) AS RN
        From person 
        left join Email on Person.ID=Email.PersonId
        ) A
WHERE A.RN = 1

Answer 2

I would use an outer apply for this, I find it more readable.

Select Person.PersonName, coalesce(Email.Email,'No email found.') as Email
From person 
outer apply (
  select top(1) Email.Email 
  from Email 
  where Person.ID=Email.PersonId
  order by <whatever suits you>
) as Email;

Answer 3

select
  P.PersonID,
  (SELECT TOP 1 E.Email FROM Email E WHERE E.PersonID = P.PersonID ORDER BY <pick your column here>)
from
  Person P

Answer 4

As it does not matter which email shows up. I think that the following one is very direct.

Select Person.PersonName,  MIN(Email.Email)
From person 
left join email 
on Person.ID=Email.PersonId
group by Person.Id, Person.PersonName