Re: Tough SQL - min, max driven

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 14 Oct 2005 20:38:16 -0700
Message-ID: <1129347496.555116.224520@z14g2000cwz.googlegroups.com>

scott_at_ORA92> SELECT * FROM table01
  2 /

    JOB_ID POSTCODE SUBURB_NA PRIORITY JOB_TYPE STATUS START_TIM END_TIME
---------- ---------- --------- ---------- ---------- --------

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

scott_at_ORA92> SELECT postcode,

  2  	    suburb_name,
  3  	    MIN (start_time) AS min_start,
  4  	    MAX (end_time) AS max_end,
  5  	    MIN (priority) AS min_priority,
  6  	    MAX (job_type) KEEP
  7  	      (DENSE_RANK FIRST ORDER BY priority)
  8  	      AS job_type,
  9  	    MAX (status) KEEP
 10  	      (DENSE_RANK FIRST ORDER BY priority)
 11  	      AS status

 12 FROM table01
 13 GROUP BY postcode, suburb_name
 14 /

  POSTCODE SUBURB_NA MIN_START MAX_END MIN_PRIORITY JOB_TYPE STATUS
---------- --------- --------- --------- ------------ ----------

---

      2122 Albury 10Oct2004 31Dec2004 1 JobType02 Status02

scott_at_ORA92> Received on Fri Oct 14 2005 - 22:38:16 CDT