Re: select first x rows from a query

From: Eugenio <eugenio.spadafora_nospam_at_compaq.com>
Date: 2000/06/09
Message-ID: <8hqspb$95k$1@mailint03.im.hou.compaq.com>#1/1

Tom,
the simplest way I found works only if you have an index on the ordering field.

SELECT col1 -- eventually use SELECT /*+ INDEX(myindex_on_col3) */
FROM mytable
WHERE ROWNUM<= X
ORDER BY col3 DESC

This is true because rownum is related to the rows before execution of sort. But, if you force the INDEX on col3 to be used in a DESC way, the sort is already done
and you'll have the rows you are looking for.

If you don't have index on the column the problem is harder than the previous one.

I haven't a parametric solution, but only a terrible statement for your case (first 3 rows), assuming that the column col3 has no duplicates (otherwise you should modify the conditions of the subqueries to find only 3 rows)

select col1
from mytable
where col3>=
  (select max(col3)
   from mytable
   where col3 <

     (select max(col3)
      from mytable
      where col3 <
        (select max(col3)
         from mytable
        )
     )

  )

--
I hope this helps

Eugenio
remove _nospam from reply address

Opinions are mine and do not necessarily reflect those of my company

=======================================================
Tom wrote in message <3940E4F8.E71B965_at_fh-coburg.de>...


>Hi everybody!

>

>I've a table with several rows. Then I've to run a query like:

>

>    SELECT col1 FROM mytable ORDER BY col3 DESC;

>

>But now I need only the first 3 rows of that resultset. Is there a way

>to do this in

>a SQL-Statement (I know: I could do that using a FOR-loop in a

>procedure, but

>this will cause some other problems ...)

>

>thanx in advance

>Tom

>

Received on Fri Jun 09 2000 - 00:00:00 CDT