asked on

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

ASKER CERTIFIED SOLUTION

scorneo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GerardMcL

ASKER

This does thank you.
I was also wondering how I could adapt this to run as a stand alone SQL query

scorneo

This will work as a stand alone sql query.
Do u have any problem at it ?