Posted on 2004-10-21
Last Modified: 2012-05-05
hi, l'm using oracle 9i and trying to use FIRST_ROWS hint, l run SQL statement as below and it didn't work as expected, it returned all rows on that table.anyone know where did i missed ? any help would be appriciate.

select /*+FIRST_ROWS(10)*/ column1 from table1


Question by:Explorer060599
    LVL 75

    Expert Comment

    by:slightwv (䄆 Netminder)
    Hints do not restrict the result set.  This just tells the optimizer to do whatever you can to get me the 1st 10 rows ASAP and get the rest of them whenever.

    To restrict what rows come back you need a where clause.

    Author Comment

    do oracle have something like "select TOP" from MS-SQL ?
    LVL 75

    Expert Comment

    by:slightwv (䄆 Netminder)
    From a quick scan of SQlServer docs (I'm not up on my SQLServer commands) I don't think it is straight forward in Oracle.  There might be some new 10g command for this (I'm not sure).

    Oracle does have a dynamic column called rownum that is generated and applied to the result set prior to clauses such as order by and group by.  It does have some limitations that can be worked around (there is also a row_number function in the datawareshousing analytics).

    All that said you can perform:
    select * from tab1 where rownum <= 10;

    Part of the limitations is that you can't perform (there are others, this is one of the big ones):
    select * from tab1 where rownum between 2 and 5; -- this is due to the fact that rownum isn't assigned until everything coes back.
    the workaround for this is:
    select * from (select rownum myrownum, t.* from tab1 t) where myrownum between 2 and 5;
    LVL 3

    Accepted Solution

    Some examples of TopN queries (note the difference between using ROWNUM and analytical functions in how they handle ties - salaries of SCOTT and FORD), as well as a more efficient way to query RowsBetweenXandY (does not use BETWEEN):

    sql>select ename, sal,
      2         rank() over (order by sal desc) rnk,
      3         dense_rank() over (order by sal desc) drnk
      4    from emp;

    ENAME            SAL       RNK      DRNK
    ---------- --------- --------- ---------
    KING            5000         1         1
    SCOTT           3000         2         2
    FORD            3000         2         2
    JONES           2975         4         3
    BLAKE           2850         5         4
    CLARK           2450         6         5
    ALLEN           1600         7         6
    TURNER          1500         8         7
    MILLER          1300         9         8
    WARD            1250        10         9
    MARTIN          1250        10         9
    ADAMS           1100        12        10
    JAMES            950        13        11
    SMITH            800        14        12

    14 rows selected.

    sql>select *
      2    from (select ename, sal
      3            from emp
      4           order by sal desc)
      5   where rownum <= 2;        

    ENAME            SAL
    ---------- ---------
    KING            5000
    SCOTT           3000

    2 rows selected.  -- FORD is not included

    sql>select ename, sal
      2    from (select ename, sal, dense_rank() over (order by sal des
      3            from emp)
      4   where rn <= 2;        

    ENAME            SAL
    ---------- ---------
    KING            5000
    SCOTT           3000
    FORD            3000

    3 rows selected.

    sql>select ename, sal
      2    from (select e.*, rownum rn
      3            from (select ename, sal
      4                    from emp
      5                   order by sal desc) e
      6           where rownum <= 6)
      7   where rn >= 5;

    ENAME            SAL
    ---------- ---------
    BLAKE           2850
    CLARK           2450

    2 rows selected.  -- this approach is more efficient than using the BETWEEN clause

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Product Review - Android Remix

    Come along for the ride with our Senior Product Manager, Brian Matis, as he reviews the Android Remix.

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now