Results 1 to 4 of 4

Thread: Query help

  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Unanswered: Query help

    I have two tables (queue1 & queue2). They both have
    the same columns (acct, date & amt). There are many
    records for the same accounts in both tables.

    I need to select a list of rows using a like operator
    returning only the newest record for each acct.

    Queue1
    Acct Date Amt
    12345 1/1/2004 100.00
    1234 1/1/2004 200.00
    123 7/21/2004 250.00
    123456 7/21/2004 150.00

    Queue2
    Acct Date Amt
    1234 1/1/2004 100.00
    12345 4/1/2004 200.00
    123456 7/20/2004 300.00
    123456 7/10/2004 50.00


    Acct like 12345% should return
    Acct Date Amt
    12345 4/1/2004 200.00
    123456 7/21/2004 150.00

    Any ideas?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can use the NOT EXISTS of the WHERE clause to create a subquery. Check the SQL Reference for details.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2004
    Posts
    2
    This works. Any one know of a more elegent way?

    SELECT
    q.acct,
    q.trans_date,
    q.amt
    FROM
    (SELECT acct, trans_date, amt FROM QUEUE1 q1
    WHERE
    q1.acct LIKE '12345%'
    UNION ALL
    SELECT acct, trans_date, amt FROM QUEUE2 q2
    WHERE q2.ACCT LIKE '12345%') q
    WHERE
    q.TRANS_DATE = (SELECT MAX(q4.trans_date)
    FROM
    (SELECT acct,trans_date FROM QUEUE1 q1
    WHERE
    q1.acct LIKE '12345%'
    UNION ALL
    SELECT acct,trans_date FROM QUEUE2 q2
    WHERE q2.ACCT LIKE '12345%') q4
    WHERE q.acct = q4.acct)

  4. #4
    Join Date
    Jun 2004
    Posts
    28
    how about:

    with tmp as (select * from queue1 where acct like '12345%' union all select * from queue2 where acct like '12345%') select * from tmp
    where dat in (select max(dat) from tmp group by acct)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •