Home » SQL & PL/SQL » SQL & PL/SQL » How to get record number of a table without using ROWNUM?
How to get record number of a table without using ROWNUM? [message #223515] Fri, 09 March 2007 04:41 Go to next message
abhijit.roy
Messages: 3
Registered: March 2007
Location: Kolkata
Junior Member
table contents:
---------------

name age
A.ROY 31
B.DAS 35
C.SEN 24


output should be:
-----------------
sl.no. name age
1 A.ROY 31
2 B.DAS 35
3 C.SEN 24

without using ROWNUM.
Re: How to get record number of a table without using ROWNUM? [message #223517 is a reply to message #223515] Fri, 09 March 2007 05:12 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Why? That's what rownum is for. So using rownum will work, unless there is some extra reqirement you didn't tell us.

So withouth knowing that exra requirement, there is no way someone can come up with a solution that really fits.
Re: How to get record number of a table without using ROWNUM? [message #223518 is a reply to message #223517] Fri, 09 March 2007 05:17 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You might, however, use analytic function to get such a result. For example,
SELECT RANK() OVER (ORDER BY name),
       name,
       age
FROM your_table;

Re: How to get record number of a table without using ROWNUM? [message #223520 is a reply to message #223515] Fri, 09 March 2007 05:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why?
Why do we keep getting these 'How can I do <x> without using the builtin functionality explicitly designed to do <x>' question?

However in the interest of providing increasingly arcane solutions to pointless questions, you can try:
SELECT row_number() over (order by 1) rnum
      ,name
      ,age
FROM   table;
or
select dbms_rowid.rowid_row_number(rowid) rnum
      ,name
      ,age
FROM   table;
Re: How to get record number of a table without using ROWNUM? [message #223521 is a reply to message #223518] Fri, 09 March 2007 06:03 Go to previous messageGo to next message
abhijit.roy
Messages: 3
Registered: March 2007
Location: Kolkata
Junior Member
thank. it works
Re: How to get record number of a table without using ROWNUM? [message #223527 is a reply to message #223521] Fri, 09 March 2007 06:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
We know it work - we wouldn't have posted them otherwise.
What we don't know (and are quite interested in) is WHY?
What's wrong with rownum?
Rownum will be quicker than any of those other solutions, and easier to understand too.
icon7.gif  Re: How to get record number of a table without using ROWNUM? [message #223528 is a reply to message #223515] Fri, 09 March 2007 06:25 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I hope nobody minds me asking what the "it" in "it works" means Razz

Re: How to get record number of a table without using ROWNUM? [message #223535 is a reply to message #223528] Fri, 09 March 2007 06:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ok. Bets are open on the 'WHY'...
My bet is 'interview question'
Second bet is OP did NOT ask the interviewer what the ridiculous answer would be he was after...

[Updated on: Fri, 09 March 2007 06:41]

Report message to a moderator

Re: How to get record number of a table without using ROWNUM? [message #223546 is a reply to message #223535] Fri, 09 March 2007 07:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
My bet is 'Homework, to get them to think', in which case our OP has failed, by getting us to do the thinking for him.
Re: How to get record number of a table without using ROWNUM? [message #223743 is a reply to message #223520] Sat, 10 March 2007 20:56 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
JRowbottom wrote on Fri, 09 March 2007 22:56
select dbms_rowid.rowid_row_number(rowid) rnum
      ,name
      ,age
FROM   table;



I know its off-topic but considering nobody is really sure what the topic is, I reckon I can get away with it.

The above might give you the desired results, provided the entire table is stored in a single block and no rows have ever been deleted. Otherwise it will cycle over 1:n (n being the number of rows that can fit in a block) and/or be missing numbers.

Ross Leishman
Re: How to get record number of a table without using ROWNUM? [message #223930 is a reply to message #223743] Mon, 12 March 2007 05:09 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yup - didn't think of that, and my test table was a diddy little one, so I didn't hit it.
Hey ho - stupid problem anyway. Cool
Previous Topic: Newbie Query
Next Topic: Converting to columns
Goto Forum:
  


Current Time: Sun Dec 11 06:27:48 CST 2016

Total time taken to generate the page: 0.08026 seconds