CODEWITHSUNDEEP
sql-server-2008
SMITH
SMITH

Reputation: 75

Select the first row of the multiple same record

The query I am using have multiple firstname,lastname for one email address.How can I select only the first row and disregard remaining. Like

        Tracy     Dugan  [email protected]
        stephen   stacy  [email protected]
        Ron       Thomas [email protected]

Result Tracy Dugan [email protected] And I have multiple rows with same email addresses.How o accomplish that

      SELECT distinct f.email_address,TITLE,FIRST_NAME,LAST_NAME FROM
      #tempFinal F,temp htc
      where f.EMAIL_ADDRESS = htc.EMAIL_ADDRESS

Upvotes: 0

Views: 64

Answers (1)

iruvar
iruvar

Reputation: 23364

Partition the resultset by email_address and use row_number to assign a sequence, then filter. Adjust the sequence criteria with the order by clause

select firstname, lastname, email_address from (
select firstname, lastname, email_address, row_number() over
(partition by email_address order by firstname, lastname) as ranker
from table ) Z where ranker = 1

Upvotes: 1

Related Questions