snippetsqlMajor

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

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

rowstheprimaryjoinmultplefirstthatreturnshowselect

Problem

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;

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#103815, answer score: 23

Revisions (0)

No revisions yet.