By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,180 Members | 1,051 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,180 IT Pros & Developers. It's quick & easy.

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

P: 50
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
Dec 6 '06 #1
Share this Question
Share on Google+
3 Replies


almaz
Expert 100+
P: 168
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:

Expand|Select|Wrap|Line Numbers
  1. create table #table(ID int identity(1,1), Name nvarchar(100))
  2. insert #table (Name) values ('Peter')
  3. insert #table (Name) values ('Andreas')
  4. insert #table (Name) values ('George')
  5. insert #table (Name) values ('Met')
  6. insert #table (Name) values ('Jack')
  7.  
  8. select AlphebeticNameOrder = row_number() over (order by Name), * 
  9. from #table
  10. order by ID
Dec 6 '06 #2

iburyak
Expert 100+
P: 1,017
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]
Dec 7 '06 #3

almaz
Expert 100+
P: 168
I changed above code a bit to convert it to MSSQL
My code is MSSQL :). But only for 2005 version of MS SQL Server.
Dec 8 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.