Home » SQL & PL/SQL » SQL & PL/SQL » nth row, last 10 rows, first 25 rows, rownum (merged 4 topics)
nth row, last 10 rows, first 25 rows, rownum (merged 4 topics) [message #287184] Tue, 11 December 2007 06:20 Go to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi

In my table i have 1 to 50 tables are there, but user want to see first 25 rows only.How?
Re: first 25 rows? [message #287190 is a reply to message #287184] Tue, 11 December 2007 06:27 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

In my table i have 1 to 50 tables are there


Is that tables or rows?

If rows then you can use keyword ROWNUM in condition.

Search for ROWNUM.

Kiran.
Re: first 25 rows? [message #287191 is a reply to message #287184] Tue, 11 December 2007 06:28 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
First of all you have to define what you call "first". Oracle is a relational database. It has no idea of "first" or "last". You need to sort the result set. In the example below it is ordered by empno:
SQL> select empno
  2       , ename
  3  from   emp
  4  order  by empno
  5  /

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.
If I only want to see the first 10 of that output, I wrap the select in an another select and limit the output by using the ROWNUM pseudo column:
SQL> ed
Wrote file afiedt.buf

  1  select *
  2  from   ( select empno
  3                , ename
  4           from   emp
  5           order  by empno
  6         )
  7* where  ROWNUM <= 10
SQL> /

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER

10 rows selected.

Key to remember here is:
- first sort
- then limit

Not the other way around, ROWNUM is retrieved before any sort is applied. This is the reason why I wrap it in an inner select.

This is in the FAQ: How does one select the TOP N rows from a table?
And if you'd search for "top N" you probably get quite some hits too.

MHE

[Updated on: Tue, 11 December 2007 06:32]

Report message to a moderator

Re: first 25 rows? [message #287231 is a reply to message #287184] Tue, 11 December 2007 08:43 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@ tondapi :

The point to understand is

Quote:

Oracle is a relational database. It has no idea of "first" or "last"


Ie, There is no impact on position of row or column in a relational DBMS like Oracle.

Thumbs Up
Rajuvan.
Retrieve only the Nth row and Finding the last 10 rows (merged 2 threads) [message #290185 is a reply to message #287184] Thu, 27 December 2007 22:39 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi

In my database we have 200 records are there.

But i want to see only 158 record.How can i see that one which function is used for this one.
Re: Retrieve only the Nth row [message #290189 is a reply to message #290185] Thu, 27 December 2007 22:45 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hi Tondapi,

In Relational database like Oracle , neither row nor column position is important .

Thumbs Up
Rajuvan.
Re: Retrieve only the Nth row [message #290192 is a reply to message #290185] Thu, 27 December 2007 22:55 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>In my database we have 200 records are there.
>But i want to see only 158 record.

You have a basket with 200 balls.
Which ball is the 158th ball??????
Finding teh last 10 rows? [message #290194 is a reply to message #290185] Thu, 27 December 2007 22:58 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi

i want to find the last 10 rows.How
Re: Finding teh last 10 rows? [message #290196 is a reply to message #290194] Thu, 27 December 2007 22:59 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>i want to find the last 10 rows.How

you have 20 balls in a basket.
Which are the "last 10 balls" in the basket?

You seem to be a VERY slow learner.
Re: Retrieve only the Nth row and Finding the last 10 rows (merged 2 threads) [message #290321 is a reply to message #290185] Fri, 28 December 2007 06:29 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

I think you asked similar kind of question before.
http://www.orafaq.com/forum/m/287184/115564/#msg_287184

Regards,
Kiran
Re: Retrieve only the Nth row and Finding the last 10 rows (merged 2 threads) [message #290334 is a reply to message #290321] Fri, 28 December 2007 06:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some can't learn.

Regards
Michel
Rownum problem? [message #290454 is a reply to message #287184] Sat, 29 December 2007 00:40 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
In my table we have 100 rows but user want to see last 20 rows.

I use rownum but ia m not able to get that one.Any one help me plz.
Re: Rownum problem? [message #290455 is a reply to message #290454] Sat, 29 December 2007 00:45 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

You asking this 10 times. Yesterday and now today also.

Kiran.
Re: Rownum problem? [message #290456 is a reply to message #290454] Sat, 29 December 2007 00:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Locked.
I will not even merge this with your other posts.
Continue in the other thread and stop reposting the same question over and over.
Re: Rownum problem? [message #290472 is a reply to message #290456] Sat, 29 December 2007 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I merged it to prevent from multipling such questions/topics.

Regards
Michel
Re: nth row, last 10 rows, first 25 rows, rownum (merged 4 topics) [message #290635 is a reply to message #287184] Sun, 30 December 2007 23:16 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

You can do same rownum

select ename,deptno,sal from (select rownum,ename,deptno,sal from emp order by rownum desc) where rownum <=20;

Kindly revert back.

Thanks
Mano
Re: nth row, last 10 rows, first 25 rows, rownum (merged 4 topics) [message #290640 is a reply to message #290635] Mon, 31 December 2007 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@spmano1983

Please, once again, read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: nth row, last 10 rows, first 25 rows, rownum (merged 4 topics) [message #290710 is a reply to message #287184] Mon, 31 December 2007 03:40 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi Michel,

What do you mean? can you explain what your r going to tell. how i should format my post? can you help me? which line exceeding above 80 characters? really i could not understand. i need your suggestion....


Thanks
Manoharan
Re: nth row, last 10 rows, first 25 rows, rownum (merged 4 topics) [message #290716 is a reply to message #290710] Mon, 31 December 2007 04:22 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Read the OraFAQ Forum Guide; pay attention to "How to format your post" section (but read the others as well).
Previous Topic: Can we create a PDF File attachment and send as a mail using PL/SQL
Next Topic: Describing Record Type created in Package Specification
Goto Forum:
  


Current Time: Thu Dec 08 12:38:56 CST 2016

Total time taken to generate the page: 0.15693 seconds