Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win



Posted on 2004-10-21
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12371750
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

ID: 12371784
do oracle have something like "select TOP" from MS-SQL ?
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12372162
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;

Accepted Solution

ToddBarry earned 400 total points
ID: 12374917
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

636 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