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


Who is Participating?
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
slightwv (䄆 Netminder) Commented:
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.
Explorer060599Author Commented:
do oracle have something like "select TOP" from MS-SQL ?
slightwv (䄆 Netminder) Commented:
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;
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.