SELECT MAX from UNION Query

Hi,

I am joining 3 queries with a UNION and when I get the results I want to only get one row for each lot determined by the MAX(PDATETIME). I can not group by the other columns as they differ and I can also not MAX these to get past this.This SQL will in turn be used by a different program which can not use analytical functions so rank or row_number are out the window too.
I have that nagging feeling that Im forgetting something very basic to fix this. Can you let me know what you think please?



SELECT    ALUM_HIST.PDATETIME,
    ALUM_HIST.LOT,
    ALUM_HIST.STAGE,
    ALUM_HIST.STEP,
    ALUM_HIST.MACH,
    ALUM_HIST.RUNNUM,
    ALUM_HIST.PASSFAIL
FROM
    WAFER.ALUM_HIST ALUM_HIST
WHERE
    ALUM_HIST.PDATETIME >= SYSDATE -4 AND ALUM_HIST.MACH LIKE 'CPT%'
UNION
SELECT
        ALD_HIST.PDATETIME, 
    ALD_HIST.LOT, 
    ALD_HIST.STAGE, 
    ALD_HIST.STEP, 
    ALD_HIST.MACH, 
    ALD_HIST.RUNNUM,
    ALD_HIST.PASSFAIL
FROM
    WAFER.ALD_HIST ALD_HIST
WHERE
    ALD_HIST.PDATETIME >= SYSDATE -4
UNION
SELECT
        GAPDEP_HIST.PDATETIME, 
    GAPDEP_HIST.LOT, 
    GAPDEP_HIST.STAGE, 
    GAPDEP_HIST.STEP, 
    GAPDEP_HIST.MACH, 
    GAPDEP_HIST.RUNNUM, 
    GAPDEP_HIST.PASSFAIL
FROM
    WAFER.GAPDEP_HIST GAPDEP_HIST
WHERE
    (GAPDEP_HIST.MACH LIKE '602%' OR GAPDEP_HIST.MACH LIKE 'COR%') AND GAPDEP_HIST.PDATETIME >= SYSDATE -4
ORDER BY LOT

Open in new window

GerardMcLAsked:
Who is Participating?
 
scorneoCommented:
Do this solves ur query ?
WITH TEMP AS
(SELECT  ALUM_HIST.PDATETIME,
    ALUM_HIST.LOT,
    ALUM_HIST.STAGE,
    ALUM_HIST.STEP,
    ALUM_HIST.MACH,
    ALUM_HIST.RUNNUM,
    ALUM_HIST.PASSFAIL
FROM
    WAFER.ALUM_HIST ALUM_HIST
WHERE
    ALUM_HIST.PDATETIME >= SYSDATE -4 AND ALUM_HIST.MACH LIKE 'CPT%'
UNION
SELECT
        ALD_HIST.PDATETIME, 
    ALD_HIST.LOT, 
    ALD_HIST.STAGE, 
    ALD_HIST.STEP, 
    ALD_HIST.MACH, 
    ALD_HIST.RUNNUM,
    ALD_HIST.PASSFAIL
FROM
    WAFER.ALD_HIST ALD_HIST
WHERE
    ALD_HIST.PDATETIME >= SYSDATE -4
UNION
SELECT
        GAPDEP_HIST.PDATETIME, 
    GAPDEP_HIST.LOT, 
    GAPDEP_HIST.STAGE, 
    GAPDEP_HIST.STEP, 
    GAPDEP_HIST.MACH, 
    GAPDEP_HIST.RUNNUM, 
    GAPDEP_HIST.PASSFAIL
FROM
    WAFER.GAPDEP_HIST GAPDEP_HIST
WHERE
    (GAPDEP_HIST.MACH LIKE '602%' OR GAPDEP_HIST.MACH LIKE 'COR%') AND GAPDEP_HIST.PDATETIME >= SYSDATE -4
ORDER BY LOT )

SELECT 
    MAXDATE,
    LOT,
    STAGE,
    STEP,
    MACH,
    RUNNUM,
    PASSFAIL
FROM TEMP,
(SELECT MAX(PDATETIME) MAXDATE FROM TEMP GROUP BY PdateTIME) TEMP2
WHERE TEMP.LOT = TEMP2.LOT

Open in new window

0
 
GerardMcLAuthor Commented:
This does thank you.
I was also wondering how I could adapt this to run as a stand alone SQL query
0
 
scorneoCommented:
This will work as a stand alone sql query.
Do u have any problem at it ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.