Home » SQL & PL/SQL » SQL & PL/SQL » Find the nth record from a table
Find the nth record from a table [message #296383] Sat, 26 January 2008 08:42 Go to next message
gaurav200x
Messages: 17
Registered: December 2007
Junior Member
I hve a query... I want to find the nth record from a table but dont have to use the column name. Can u help me with the query?
Dun have to use PL/SQL. Use a single SQL query.
Re: Find the nth record from a table [message #296384 is a reply to message #296383] Sat, 26 January 2008 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can do it if YOU can give me the nth apple in my basket.

Regards
Michel
Re: Find the nth record from a table [message #296386 is a reply to message #296384] Sat, 26 January 2008 08:52 Go to previous messageGo to next message
gaurav200x
Messages: 17
Registered: December 2007
Junior Member
Michel Cadot wrote on Sat, 26 January 2008 20:19
I can do it if YOU can give me the nth apple in my basket.

Regards
Michel



Are u asking me the value of n ? If so, take it anything like n = 25 from employees table

If u mean something else, please explain..
Re: Find the nth record from a table [message #296388 is a reply to message #296386] Sat, 26 January 2008 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I am not "u" I am Michel.
Don't use IM speak.
Read and follow OraFAQ Forum Guide.

I am not asking for n.
Now you give me n=25, I ask you to give the 25th apple in my basket.

Regards
Michel
Re: Find the nth record from a table [message #296390 is a reply to message #296383] Sat, 26 January 2008 09:31 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
This question is asked on a ridiculously regular basis.
Doing a simple search on google for oracle nth record returns nearly 8000 hits. I bet one of them will have exactly what you need.
Re: Find the nth record from a table [message #296392 is a reply to message #296388] Sat, 26 January 2008 09:36 Go to previous messageGo to next message
gaurav200x
Messages: 17
Registered: December 2007
Junior Member
Michel Cadot wrote on Sat, 26 January 2008 20:28
I am not "u" I am Michel.
Don't use IM speak.
Read and follow OraFAQ Forum Guide.

I am not asking for n.
Now you give me n=25, I ask you to give the 25th apple in my basket.

Regards
Michel



Dear Mr. Michel,
I would appreciate if you can explain in simple english what exactly do you mean. I am unable to follow your riddles. I suspect some miscommunication between us.
Re: Find the nth record from a table [message #296393 is a reply to message #296390] Sat, 26 January 2008 09:40 Go to previous messageGo to next message
gaurav200x
Messages: 17
Registered: December 2007
Junior Member
pablolee wrote on Sat, 26 January 2008 21:01
This question is asked on a ridiculously regular basis.
Doing a simple search on google for oracle nth record returns nearly 8000 hits. I bet one of them will have exactly what you need.


Sir,
I am a newbie on this forum and i don't know if anyone has asked this question. Even i tried to think of a logical answer, but was clueless...

Let me re-phrase the question. My friend asked me if one can find out the content of a table for any given row number from a table when u don't know the number of columns in a table or it is exceedingly large, say a 100.

So, if i want to know the 31st row of the employees table without querying the data dictionary, then is it possible to do so.

I am serious about this question and hence wants to know if such a query is possible or not. Thanks for any support.
Re: Find the nth record from a table [message #296394 is a reply to message #296393] Sat, 26 January 2008 09:44 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
OK, back to analogy again.
If I have a bag of balls. Which ball is the first ball. How do you identify any given ball in that bag with a list position? i.e. How do you identify any particular row in the employees table as being the 31st. What identifies THAT ROW as being the 31st. At this point, only you can answer this question.
Re: Find the nth record from a table [message #296395 is a reply to message #296394] Sat, 26 January 2008 09:47 Go to previous messageGo to next message
gaurav200x
Messages: 17
Registered: December 2007
Junior Member
pablolee wrote on Sat, 26 January 2008 21:14
OK, back to analogy again.
If I have a bag of balls. Which ball is the first ball. How do you identify any given ball in that bag with a list position? i.e. How do you identify any particular row in the employees table as being the 31st. What identifies THAT ROW as being the 31st. At this point, only you can answer this question.


well if u use rownum and give it an alias "Rank" , then it will number the first row as 1 and the last row as 108 (i think), so accordingly, 31st row will fall in between. Is this fine?
Re: Find the nth record from a table [message #296396 is a reply to message #296383] Sat, 26 January 2008 10:07 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Is this fine?
It is fine if you want some random row returned.
Re: Find the nth record from a table [message #296400 is a reply to message #296395] Sat, 26 January 2008 11:07 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Once again, search for nth record or nth row. there are many, many examples of how2 you do Top n analysis. If the examples do not suit you, post back with what you have tried and why it doesn't fit your requirements along with a test case to emulate your situation and a description of what you expect and why.
Re: Find the nth record from a table [message #296404 is a reply to message #296400] Sat, 26 January 2008 11:43 Go to previous messageGo to next message
gaurav200x
Messages: 17
Registered: December 2007
Junior Member
pablolee wrote on Sat, 26 January 2008 22:37
Once again, search for nth record or nth row. there are many, many examples of how2 you do Top n analysis. If the examples do not suit you, post back with what you have tried and why it doesn't fit your requirements along with a test case to emulate your situation and a description of what you expect and why.


What i was looking for was.... (i got it from orkut)

select*from tbl_1 a where &n=(select count(rowid) from tbl_1 b where a.rowid>=b.rowid)
OR

select * from (select rownum id,emp.* from emp) e where e.id=&n

However, i didn't quite understand the first query.
Re: Find the nth record from a table [message #296409 is a reply to message #296404] Sat, 26 January 2008 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
However, i didn't quite understand the first query.

I understand that.
What do you think it returns?
And what do you think the second one returns?

Regards
Michel
Re: Find the nth record from a table [message #296411 is a reply to message #296404] Sat, 26 January 2008 12:04 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
(i got it from orkut)
Well, the immediate references that I could find for orkut were that it is a social networking site. Do you think that using social networking sites for technical requirements is a good idea?. You did a search on nth row and that was the best that you could come up with? C'mon, put at least a bit of thought and effort in.
Re: Find the nth record from a table [message #296413 is a reply to message #296411] Sat, 26 January 2008 12:30 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that Gaurav200x doesn't understand what the problem is.

So, try to imagine something like this: a table in a relational database table is like a basket full of apples. It means that records in a table do not have any particular order - there's no "row number" which would tell you which record was first, which one was entered second etc. (unless there's a column which will enable you to gather such an information, as a sequence or a timestamp).

Saying that "you want 25th apple" is meaningless. But, saying "I want apple which is 25th when we order them by weight", that's something completely different. You'd take a libra (scale), determine weight of each apple and order them on a table. Then you'd be able to find the 25th one.

The same goes for table records: you should know what that 25th record means; are those employees ordered by their names? Salaries? Dates when they were employed? Doesn't matter, as long as you ORDER them BY.
Re: Find the nth record from a table [message #296416 is a reply to message #296383] Sat, 26 January 2008 13:50 Go to previous messageGo to next message
gaurav200x
Messages: 17
Registered: December 2007
Junior Member
@michel

Both return the same... that i know. I think the first query implements it through
co-related subquery. However, i dont get the a.rowid >= b.rowid part.



@pablolee
Well, in case, u were unfamiliar with orkut before this, orkut has been extensively used for technical purposes, marketing, and getting and giving help... So, i don't think that by merely naming something as 'social networking engine', its functionality is restricted.

p.s. I did put in efforts before seeking help online... In fact, i had come up with the idea similar to the 2nd query...

but, i was working on something like...

select rownum as rank, * from employees

which was returning errors



@Littlefoot

That's quite right that i didn't get the same, earlier and me being a NEWBIE, have to learn a lot, still. Thanks for clarifying the same. However, right now, i am only looking for the 25th apple, starting the count from the 1st apple.

Its just that whichever way u count, the crux is that u don't know the column names and hence, u got to write the sub query for that.

Now, if u can, can u explain me the first solution , mentioned in my post (message #13)

thanks.
Re: Find the nth record from a table [message #296417 is a reply to message #296383] Sat, 26 January 2008 14:04 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>starting the count from the 1st apple.

First based upon which metric & in which order (ascending or descending)?

Which still leaves the unanswered question, "Which apple in the basket is the 1st apple?".

gaurav200x,
Tell us how to programatically identify the 1st apple; especially without specifying any column name.
Re: Find the nth record from a table [message #296418 is a reply to message #296417] Sat, 26 January 2008 14:14 Go to previous messageGo to next message
gaurav200x
Messages: 17
Registered: December 2007
Junior Member
anacedent wrote on Sun, 27 January 2008 01:34
>starting the count from the 1st apple.

First based upon which metric & in which order (ascending or descending)?

Which still leaves the unanswered question, "Which apple in the basket is the 1st apple?".

gaurav200x,
Tell us how to programatically identify the 1st apple; especially without specifying any column name.



when u write select * from employees. You get the full table. Start counting from the first row and then count till 25... that is what i need....

To make my query simpler, please explain this....

select *
from tbl_1 a
where &n = (select count(rowid)
from tbl_1 b
where a.rowid>=b.rowid);

Re: Find the nth record from a table [message #296420 is a reply to message #296418] Sat, 26 January 2008 14:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Start counting from the first row and then count till 25...

This is what we tell us, there is no first.
When you query the table, whatever the tests you can made, you can never know which one will be the first one (unless you specify an order) and even less the next ones.
If you search a little bit in this forum, you'll see many examples showing that you can get different result even if when YOU do it you get the same answer.

To answer your question: do you know what is rowid?

Regards
Michel

[Updated on: Sat, 26 January 2008 14:47]

Report message to a moderator

Re: Find the nth record from a table [message #296421 is a reply to message #296418] Sat, 26 January 2008 19:46 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
4 balls in a pot, red, green blue and white.
Which one is the first one. which one is 2nd, 3rd an 4th.

[Updated on: Sat, 26 January 2008 19:47]

Report message to a moderator

Re: Find the nth record from a table [message #296424 is a reply to message #296421] Sat, 26 January 2008 22:08 Go to previous messageGo to next message
gaurav200x
Messages: 17
Registered: December 2007
Junior Member
Yes... yes ! I got it from the previous explanation. Can u be kind enough to explain the below mentioned query.




select *
from tbl_1 a
where &n = (select count(rowid)
from tbl_1 b
where a.rowid>=b.rowid);



[i]p.s. Michel, rowid is the physical address of the row assigned by Oracle. I'm unable to understand the inner subquery.
Re: Find the nth record from a table [message #296425 is a reply to message #296424] Sat, 26 January 2008 23:07 Go to previous messageGo to next message
prtz
Messages: 11
Registered: January 2008
Junior Member
I doubt if the query you posted gives you the nth row in a table. I guess the query returns all such rows in tbl_1 that have exactly n rowids in tbl_2 less than or equal to their own rowids. Here n is a number you specify. I wonder how this query returns the nth row of a table (or which table, for that matter).

Please review closely the responses of other forum members, who are trying to explain to you that the nth row of a table is pretty meaningless unless you are ordering the resultset by some column.

ciao,
prtz
Re: Find the nth record from a table [message #296426 is a reply to message #296425] Sat, 26 January 2008 23:14 Go to previous messageGo to next message
gaurav200x
Messages: 17
Registered: December 2007
Junior Member
prtz wrote on Sun, 27 January 2008 10:37
I doubt if the query you posted gives you the nth row in a table. I guess the query returns all such rows in tbl_1 that have exactly n rowids in tbl_2 less than or equal to their own rowids. Here n is a number you specify. I wonder how this query returns the nth row of a table (or which table, for that matter).

Please review closely the responses of other forum members, who are trying to explain to you that the nth row of a table is pretty meaningless unless you are ordering the resultset by some column.

ciao,
prtz



i tried it and it does return the nth row. may it is meaningless to query the nth row, without an ORDER BY clause, yet, all i want is an explanation to the above query. If someone knows that, then please provide the same. Thanks.
Re: Find the nth record from a table [message #296427 is a reply to message #296426] Sat, 26 January 2008 23:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you understand?
You know rowid is (a representation of) physical address.
So "a.rowid>=b.rowid" means all rows in table with alias "b" with physical address representation is less or equal than the physical address representation of the row in table with alias "a".
"count(*)" means I count these rows (for each row in table with alias "a").
"&n = (select count(*)...)" means I keep only the rows (of table with alias "a") for which this count is exactly &n.

This query doesn't use ORDER BY but defines an order using ">=": the order of physical row address representation.

Regards
Michel
Re: Find the nth record from a table [message #296440 is a reply to message #296383] Sun, 27 January 2008 07:46 Go to previous messageGo to next message
KrishnaBoppana
Messages: 12
Registered: March 2007
Location: Boston, MA
Junior Member
I am not sure what the purpose is, however one suggestion -

You can use rownum column, that is not part of a table however it is a hidden column in the result set. So, if you sort the result set, for example, desc, asc, then you can use rownum = clause to get a specific record in the result set.

If you are not sure how to get something out of a relational table, or seems impossible to get it, you are probably asking something that relational structures do not support.

-Krishna

[Updated on: Sun, 27 January 2008 09:13] by Moderator

Report message to a moderator

Re: Find the nth record from a table [message #296448 is a reply to message #296440] Sun, 27 January 2008 09:14 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Blablabla.

Regards
Michel
Previous Topic: select 1
Next Topic: ORA-01427 single row subquery returns more than one row
Goto Forum:
  


Current Time: Fri Dec 09 19:48:09 CST 2016

Total time taken to generate the page: 0.11359 seconds