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?

You can use the NOT EXISTS of the WHERE clause to create a subquery. Check the SQL Reference for details.

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)

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)