DaveSlash - 1 year ago 64
SQL Question

# DB2 return first match

In DB2 for i (a.k.a. DB2/400) at V6R1, I want to write a SQL SELECT statement that returns some columns from a header record and some columns from ONLY ONE of the matching detail records. It can be ANY of the matching records, but I only want info from ONE of them. I am able to accomplish this with the following query below, but I'm thinking that there has to be an easier way than using a WITH clause. I'll use it if I need it, but I keep thinking, "There must be an easier way". Essentially, I'm just returning the firstName and lastName from the Person table ... plus ONE of the matching email-addresses from the PersonEmail table.

Thanks!

    with theMinimumOnes as (
select personId,
min(emailType) as emailType
from PersonEmail
group by personId
)
select p.personId,
p.firstName,
p.lastName,
from Person p
left outer join theMinimumOnes tmo
on tmo.personId = p.personId
left outer join PersonEmail pe
on pe.personId = tmo.personId
and pe.emailType  = tmo.emailType

1   Bill                            Ward                            p1@home.com
2   Tony                            Iommi                           p2@cell.com
3   Geezer                          Butler                          p3@home.com
4   John                            Osbourne                        -


This sounds like a job for row_number():
select p.personId, p.firstName, p.lastName, pe.emailAddress