Tough SQL - min, max driven

From: <vamshireddy01_at_gmail.com>
Date: 13 Oct 2005 23:38:09 -0700
Message-ID: <1129271889.170800.273240@z14g2000cwz.googlegroups.com>

TABLE01

---

JOB_ID	number(10) PRIMARK KEY
POSTCODE	number(10)
SUBURB_NAME	varchar(50)
PRIORITY	number(10)
JOB_TYPE	varchar(50)
STATUS	        varchar(30)
START_TIME	DATA
END_TIME	        DATE
================================

Example TABLE Data

---

1 2122 Albury 6 JobType01 Status01 11Oct2004 31Dec2004 2 2122 Albury 1 JobType02 Status02 10Oct2004 12Oct2004

---

Out of this data what I need IN-A-ROW is:
(1) Group by {POSTCODE,SUBURB}.

    In example above {2122, Albury}
(2) Get min(START_TIME)

    In example above {10Oct2004}
(3) Get max(END_TIME)

    In example above {31Dec2004}
(4) Get min(PRIORITY)

    In example above {1}
-------- THEN THE HARD BIT --------
(4) Get {JOB_TYPE, STATUS} from row

    that has min(PRIORITY)
    In example it is {JobType02, Status02}

---

==============RESULT OF SOME CLEVER SQL===========

---

2122 Albury 10Oct2004 31Dec2004 1 JobType02 Status02

Couldn't figure out a decent SQL with joins and sub-queries. I'm trying to avoind going with a procedure/function.

Thanks & Cheers,
Gurram Received on Fri Oct 14 2005 - 01:38:09 CDT