CODEWITHSUNDEEP
sqlibm-midrangedb2-400
Dave Ford
Dave Ford

Reputation: 363

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,
           pe.emailAddress
      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

    PERSONID   FIRSTNAME                       LASTNAME                        EMAILADDRESS
           1   Bill                            Ward                            [email protected] 
           2   Tony                            Iommi                           [email protected] 
           3   Geezer                          Butler                          [email protected] 
           4   John                            Osbourne                        -

Upvotes: 2

Views: 2751

Answers (2)

CRPence
CRPence

Reputation: 1259

If which row would be selected from the PersonEmail file is truly immaterial, then there is little reason to perform either of a summary query or an OLAP query to select that row; ordering is implied in the former per the MIN aggregate of the CTE, and order is explicitly requested in the latter. The following use of FETCH FIRST clause should suffice, without any requirements for ORDER of data in the secondary file [merely any matching row; albeit likely to be the first or last, depending on the personId keys, although dependent entirely on the query implementation which could even be without the use of a key]:

  select p.personId, p.firstName, p.lastName 
       , pe.emailAddress 
  from Person as p 
  left outer join lateral 
     ( select pe.* 
       from PersonEmail pe 
       where pe.personId = p.personId 
       fetch first 1 row only 
     ) as pe 
  on p.personId = pe.personId

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This sounds like a job for row_number():

select p.personId, p.firstName, p.lastName, pe.emailAddress
from Person p left outer join
     (select pe.*,
             row_number() over (partition by personId order by personId) as seqnum
      from PersonEmail pe
     ) pe
     on pe.personId = tmo.personId and seqnum = 1;

Upvotes: 3

Related Questions