OraFAQ Forum: SQL & PL/SQL » Fetching middle rows from the database

Home » SQL & PL/SQL » SQL & PL/SQL » Fetching middle rows from the database

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Fetching middle rows from the database [message #342659]

Sat, 23 August 2008 14:55

trgita@gmail.com
Messages: 2
Registered: August 2008

Junior Member

I have a question, I would like to know how to fetch, in between rows from the table. Say for example, I have 1000 records in my table named employee. I would like to fetch the records between 500 and 600. How can I do that?

I read few forums and came to know that fetching top, bottom or middle rows from the table is meaningless unless we use orderby clause. Bcos there is no particular sequence available in the database.So if I use rownum pseudo column name to find top n bottom records using orderby clause.

Can somebody help me out with the answer on how to fetch the middle few hundreds of records.

Thanks.

Report message to a moderator

Re: Fetching middle rows from the database [message #342660 is a reply to message #342659]

Sat, 23 August 2008 15:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

The question is nonsensical.
Rows in a table have NO inherent order.
Rows in a table are like balls in a basket.
If a basket has three red ball, which red ball is the middle ball?

What business problem are you trying to solve & based upon what value of which metric would an independent observer conclude the problem has been solved (or not)?

[Updated on: Sat, 23 August 2008 15:00] by Moderator

Report message to a moderator

Re: Fetching middle rows from the database [message #342661 is a reply to message #342660]

Sat, 23 August 2008 15:13

trgita@gmail.com
Messages: 2
Registered: August 2008

Junior Member

Hi, I dont have any business problem. But this is one of the interview questions for my friend. So I am curious to know the answer for this one. I can understand that the records in the database is like balls in the basket. But I read that we can fetch the top, bottom or middle rows when any particular column is ordered by using orderby clause.

Thanks.

Report message to a moderator

Re: Fetching middle rows from the database [message #342663 is a reply to message #342659]

Sat, 23 August 2008 15:39

flyboy
Messages: 1903
Registered: November 2006

Senior Member

> Bcos there is no particular sequence available in the database.So if I use rownum pseudo column name to find top n bottom records using orderby clause.

>But I read that we can fetch the top, bottom or middle rows when any particular column is ordered by using orderby clause.

ROWNUM is pseudocolumn, not the real table column. It is just the rank in which the row was fetched, so it is not deterministic - it may change with each query execution. So it is not "any particular column" applicable for ordering.
However if you would have a real table column in the table to sort by, you may inspire by this thread on AskTom: paging through results on the web..

Report message to a moderator

Previous Topic:	Difference between self join and natural join
Next Topic:	having doubt with USING clause in JOINS

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 06:23:57 CST 2024