GROUP BY when MIN() is nested in a CASE

Hi all,
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
GO

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
UNION
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
GO

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?
MoriPiAsked:
Who is Participating?
 
stevejelConnect With a Mentor Commented:
Actually, having reread, try below, it works in MySQL.
Steve

SELECT I.ID,
MIN(case when I.PHYSICAL_POSTCODE = '3000' then I.PRIORITY_DATE else 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(+)
GROUP BY I.ID
ORDER BY PRIORITY_DATE ASC, I.LISTING_NAME ASC, I.ID ASC
0
 
stevejelCommented:
what database are you using, you can use a group by in this situation with MySQL.

Initial thought would be try using

... I.PHYSICAL_POSTCODE = '3000' then MIN(I.PRIORITY_DATE) else ...

Steve
0
 
MikeOM_DBACommented:

I would try something more like this:

SELECT I.ID
         , CASE WHEN I.PHYSICAL_POSTCODE = '3000'
           THEN I.PRIORITY_DATE
            ELSE Z.ZONING_DATE END AS PRIORITY_DATE
FROM ID I, PRODUCT P, (
     SELECT ID, ZONEKEY, MIN(ZONING_DATE) ZONING_DATE FROM ZONE
      GROUP BY ID, ZONEKEY)  Z
WHERE I.HEADING_ID = 24368
    AND I.ID = P.ID
    AND I.ID = Z.ID(+)
    AND (I.PHYSICAL_POSTCODE = 3000
      OR (SUBSTR(Z.ZONEKEY(+),3,4) = '3000')
      OR  Z.ZONEKEY(+) = '1')
ORDER BY PRIORITY_DATE ASC, I.LISTING_NAME ASC, I.ID ASC;
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
MoriPiAuthor Commented:
I have pondered both your solutions.
MikeOM_DBA; I don't think this will get rid of duplicates between I and Z; however it would get rid of duplicates on Z.

stevejel; Booyah! On the money right there! exactly the solution I needed, and I should have thought of it myself.  Spot on! :)
0
 
stevejelCommented:
Mike, can you explain why the subquery is neccessary in this question?

Steve
0
 
stevejelCommented:
ha, simulpost!
0
 
MoriPiAuthor Commented:
Indeed!
Very weird.
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.