Any Inbuilt function like Oracle's ROWNUM to retrive the order of records

[Reshmi Jacob]

Hi all

I would like to know the order in which the records are saved in a table. I am a beginner in SQL, in Oracle we have the ROWNUM to retrive the same. Any option for this in SQL Server???


Regards
Reshmi

[almaz]

Hi all

I would like to know the order in which the records are saved in a table. I am a beginner in SQL, in Oracle we have the ROWNUM to retrive the same. Any option for this in SQL Server???


Regards
Reshmi

There is no consistent way to get the order of records in DB (unless you are using a clustered index for the table and select rows with "order by" clustered index).
Nevertheless, in SQL Server 2005 there is a great ROW_NUMBER ranking function that allows you to enumerate resulting rows over some order. Simplest example:

create table #table(ID int identity(1,1), Name nvarchar(100))
insert #table (Name) values ('Peter')
insert #table (Name) values ('Andreas')
insert #table (Name) values ('George')
insert #table (Name) values ('Met')
insert #table (Name) values ('Jack')
 
select AlphebeticNameOrder = row_number() over (order by Name), * 
from #table
order by ID

[iburyak]

I changed above code a bit to convert it to MSSQL

[PHP]create table #table(ID int identity(1,1), Name nvarchar(100))
go
insert #table (Name) values ('Peter')
insert #table (Name) values ('Andreas')
insert #table (Name) values ('George')
insert #table (Name) values ('Met')
insert #table (Name) values ('Jack')

select IDENTITY(int, 1,1) AS ID_Num, name into #numbered_rows from #table order by name

select * from #numbered_rows[/PHP]

[almaz]

I changed above code a bit to convert it to MSSQL

My code is MSSQL :). But only for 2005 version of MS SQL Server.