GROUP BY when MIN() is nested in a CASE
Posted on 2006-10-19
I have two tables, which i've linked, and I need to get unique id's with the earliest PRIORITY_DATE returned for each ID, ordered by
Ordinarily this would be simple combination of GROUP BY and MIN(PRIORITY_DATE).
However there are 3 senarios where this occurs; each may return a date value and return that value to the result. I only want the one with the earliest PRIORITY_DATE.
If the first condition is met, it needs to return a date from the "I" table if the others fit, it needs to return a date off the "Z" table; then sorting needs to be performed on PRIORITY_DATE ASC.
I've contrived this query which does all I need, except it returns duplicate rows where multiple entries exist on Z and on Z and I. I need ID to be unique on the return set:
SELECT I.ID, case when I.PHYSICAL_POSTCODE = '3000' then I.PRIORITY_DATE else MIN(Z.ZONING_DATE) end as PRIORITY_DATE
FROM ID I, PRODUCT P, ZONE Z
WHERE I.HEADING_ID = 24368 AND
(I.PHYSICAL_POSTCODE = 3000
or (SUBSTR(Z.ZONEKEY,3,4) = '3000')
or Z.ZONEKEY = '1')
AND I.ID = P.ID
AND I.ID = Z.ID(+)
ORDER BY PRIORITY_DATE ASC, I.LISTING_NAME ASC, I.ID ASC
I don't seem to be able to put a group by in there because its only grouping on one of the members of the conditional, and is returning an error.
[group by used: GROUP BY I.IBL_ID]
I can get rid of the duplicates of Z table by writing:
SELECT I.ID, I.PRIORITY_DATE
FROM ID I, PRODUCT P
WHERE I.HEADING_ID = 24368 AND I.PHYSICAL_POSTCODE = 3000
AND I.ID = P.ID
SELECT J.ID, MIN(Y.ZONING_DATE) as PRIORITY_DATE
FROM IBL J, PRODUCT Q, ZONE_SQLLDR Y
WHERE J.HEADING_ID = 24368
AND J.ID = Q.ID
AND J.ID = Y.ID(+)
AND ((SUBSTR(Y.ZONEKEY,3,4) = '3000')
or Y.ZONEKEY = '1')
GROUP BY J.ID
ORDER BY PRIORITY_DATE ASC, ID ASC
However this will still allow duplicates to occur between values returned I and values returned by the Z table. Also it almost doubles the execution time.
Can anyone help me out by letting me how to GROUP this function to get the MIN to work on the original function?