Re: Quick SQL question

[rob@xxxxxxxxx]

Given:

SELECT * FROM MASTER
....+....1.... 
    MASTERKEY 
            1 
            2 
            3 
********  End of data

SELECT * FROM DETAIL
MASTERKEY       DETAILKEY
        1               1
        1               2
        3               1
        3               2
        3               3
        4               1
****  End of data  ******

WITH T1 AS (SELECT MASTERKEY, MAX(DETAILKEY) AS MAXDETAILKEY
FROM QTEMP/DETAIL 
GROUP BY MASTERKEY 
ORDER BY MASTERKEY) 
SELECT MASTER.MASTERKEY, IFNULL(MAXDETAILKEY,0) 
FROM QTEMP/MASTER LEFT OUTER JOIN T1 
ON MASTER.MASTERKEY = T1.MASTERKEY 
UNION 
(SELECT T1.MASTERKEY, MAXDETAILKEY 
FROM T1 EXCEPTION JOIN QTEMP/MASTER 
ON T1.MASTERKEY = MASTER.MASTERKEY) 

+....1....+....2....+....3 
MASTERKEY          IFNULL 
        1               2 
        2               0 
        3               3 
        4               1 
****  End of data  ********



Rob Berendt