Re: Tough SQL - min, max driven

From: sim <jmenker_at_muenster.de>
Date: 14 Oct 2005 04:41:48 -0700
Message-ID: <1129290108.094422.253820@z14g2000cwz.googlegroups.com>

You could try something like this:

SELECT	 Z.POSTCODE,
		 Z.SUBURB_NAME,
		 X.MIN_TIME,
		 X.MAX_TIME,
		 Z.PRIORITY,
		 Z.JOB_TYPE,
		 Z.STATUS

FROM
(

SELECT	 A.POSTCODE,
		 A.SUBURB_NAME,
		 MIN(A.START_TIME) MIN_TIME,
		 MAX(A.END_TIME) MAX_TIME
FROM	 TEST_TABLE01 A
GROUP BY A.POSTCODE,
	  	 A.SUBURB_NAME

) X,
(

SELECT	 A.POSTCODE,
		 A.SUBURB_NAME,
		 MIN(A.PRIORITY) MIN_PRIO
FROM	 TEST_TABLE01 A
GROUP BY A.POSTCODE,
		 A.SUBURB_NAME

) Y,
TEST_TABLE01 Z

WHERE Z.POSTCODE = X.POSTCODE
AND	  Z.SUBURB_NAME = X.SUBURB_NAME
AND   Z.POSTCODE = Y.POSTCODE
AND	  Z.SUBURB_NAME = Y.SUBURB_NAME
AND	  Z.PRIORITY = Y.MIN_PRIO

Tested with only your example data. May therefore still be incoorect.

sim Received on Fri Oct 14 2005 - 06:41:48 CDT