Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Retrieving first row of a query in Oracle SQL

Re: Retrieving first row of a query in Oracle SQL

From: <fitzjarrell_at_cox.net>
Date: Wed, 29 Aug 2007 08:37:39 -0700
Message-ID: <1188401859.894088.172500@g4g2000hsf.googlegroups.com>


On Aug 29, 10:13 am, digory <dig..._at_gmx.net> wrote:
> Hi
>
> I have a general Oracle SQL question. I often come across a problem
> when trying to select more than a single column of row that is the
> result of a MIN or MAX. Suppose I have a table T with the following
> columns:
>
> id NUMBER -- my unique primary key
> dat DATE -- some date, not unique
> cnt NUMBER -- some number, not unique
> stuff BLOB -- one or more columns of large data
>
> I want to retrieve all the columns of the row with the minimum value
> for dat (as a primary ordering key) and a minimum value for cnt (as a
> secondary ordering key). With other words, I want the first row of the
> following query:
>
> SELECT id, dat, cnt, stuff
> FROM T
> ORDER BY dat ASC, cnt ASC
>
> Of course, I could simply execute the query above, pick the first row
> and throw away all the rest of the rows of the result set. However,
> this is far too inefficient, especially since stuff is a BLOB.
>
> So I need some way to tell Oracle I'm only interested in the first
> row. Is there any simple way to do this?
>
> Let's try rewriting the query:
>
> SELECT id, dat, cnt, stuff
> FROM T
> WHERE cnt = (
> SELECT MIN (cnt)
> FROM T
> WHERE dat = (
> SELECT MIN(dat)
> FROM T
> )
> )
>
> This does not work since cnt is not unique, there may be more than a
> single row for a given dat. In my case, I know that this will never
> happen (the combination dat/cnt is always unique), but Oracle doesn't
> know that.
>
> SELECT id, dat, cnt, stuff
> FROM T
> WHERE cnt IN (
> SELECT MIN (cnt)
> FROM T
> WHERE dat = (
> SELECT MIN(dat)
> FROM T
> )
> )
>
> That works in this example because I know it will never return more
> than a single row. However, it's very ugly and unreadable SQL for such
> a simple thing!
>
> There MUST be a better way to do it! Can you help?
>
> Thanks in advance,
> Peter

  SELECT id, dat, cnt, stuff
   FROM T
   WHERE (cnt,dat) IN (

      SELECT MIN (cnt), MIN(dat)
      FROM T
      WHERE dat = (
         SELECT MIN(dat)
         FROM T
      )

   )

David Fitzjarrell Received on Wed Aug 29 2007 - 10:37:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US