ROWNUM In Oracle

Is there any way to implement ROWNUM in Oracle to select row no.
Is there any way to select the row no when retrieving records as
1 Tv
2 Fridge
3 RadioHi,
There is no concept of Rownum in sql server.
But you could write ur own query to get the serial number.
Use the below script as sample:-
create table item(item_code varchar(05))
go
insert into item values('a1')
insert into item values('a2')
insert into item values('a3')
insert into item values('a4')
go
SELECT (SELECT COUNT(i.item_code)
FROM item i
WHERE i.item_code >= o.item_code ) AS RowID,
item_code
FROM item o
ORDER BY RowID
Thanks
Hari
SQL Server MVP
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:3BDAC82C-4AB5-4631-ABAE-AB693B0E1312@.microsoft.com...
> Is there any way to implement ROWNUM in Oracle to select row no.
> Is there any way to select the row no when retrieving records as
> 1 Tv
> 2 Fridge
> 3 Radio|||Hi
There is not a ROWNUM function in SQLServer 2000 using an identity column is
usually the alternative. If you want to rank your values then you could use
a
construct like:
e.g
SELECT ( SELECT COUNT(*) FROM MyTable T WHERE t.id <= M.id ) AS Rank,
col1, col2
FROM MyTable M
These links may also help.
http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
http://www.microsoft.com/resources/...r />
0761.mspx
http://www.microsoft.com/sql/evalua...pare/oracle.asp
John
"Renjith" wrote:

> Is there any way to implement ROWNUM in Oracle to select row no.
> Is there any way to select the row no when retrieving records as
> 1 Tv
> 2 Fridge
> 3 Radio|||Hi
If it is a big table then the count(*) as inner query will create
performance problem '
"John Bell" wrote:
> Hi
> There is not a ROWNUM function in SQLServer 2000 using an identity column
is
> usually the alternative. If you want to rank your values then you could us
e a
> construct like:
> e.g
> SELECT ( SELECT COUNT(*) FROM MyTable T WHERE t.id <= M.id ) AS Rank,
> col1, col2
> FROM MyTable M
> These links may also help.
> http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
> http://www.microsoft.com/resources/.../>
/c0761.mspx
> http://www.microsoft.com/sql/evalua...pare/oracle.asp
> John
> "Renjith" wrote:
>|||Hi
It may, and indexing would reduce the problem.
You can also do something like:
CREATE TABLE MyTest ( id int not null identity(1,1), val char(1))
INSERT INTO MyTest ( val )
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
DELETE FROM MyTest where val = 'C'
SELECT m.id, COUNT(*) as Rank, m.val
FROM MyTest m
JOIN MyTest r ON R.id <= M.id
GROUP BY m.id, M.val
ORDER BY 2
Another alternative would be do deligate the numbering to the client.
John
"Renjith" wrote:
> Hi
> If it is a big table then the count(*) as inner query will create
> performance problem '
> "John Bell" wrote:
>|||Hi
You may want to look at Itzik Ben-Gan's articles in the May 2005 SQL
Server Magazine.
http://www.windowsitpro.com/Article...5828/45828.html
http://www.windowsitpro.com/Article...2302/42302.html
http://www.windowsitpro.com/Article...2646/42646.html
John