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 -> [Q] Retrieving first row of a query in Oracle SQL

[Q] Retrieving first row of a query in Oracle SQL

From: digory <digory_at_gmx.net>
Date: Wed, 29 Aug 2007 08:13:52 -0700
Message-ID: <1188400432.362673.299670@r34g2000hsd.googlegroups.com>


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 Received on Wed Aug 29 2007 - 10:13:52 CDT

Original text of this message

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