Click here to Skip to main content
14,384,957 members
Rate this:
Please Sign up or sign in to vote.
Hi !

I require a Auto Serial No from SQL Query.
Like (SQL Query)
select empName,Address from Employees

Out Put
1 Ram New Delhi
2 Shyam UP
3 Sohan HR
...............
..............
means 1,2,3 should auto generate depends on No of SQL Rows
Posted
Updated 6-Aug-19 21:42pm
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

Try:
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS RowNum,* FROM myTable
   
Comments
LebneizTech 10-Mar-12 7:37am
   
Sir, Message Shows :

Server: Msg 195, Level 15, State 10, Line 1
'ROW_NUMBER' is not a recognized function name.
OriginalGriff 10-Mar-12 7:50am
   
Which version of SQL are you using?
LebneizTech 10-Mar-12 9:54am
   
SQL 2000
OriginalGriff 10-Mar-12 10:37am
   
There is no nice way to do it in SQL2000 (ROW_NUMBER was introduced at SQL2005) - it can be done, but if you have a lot of data it is going to be painfully slow as you have to either generate a temporary table or nest a select if you have a sorted column. If you don't have a sorted column, then it has to be a temporary table, I'm afraid.

Why do you want to do this? Would it not be easier to add an Identify column to the table?
Dileep Goplakrishnan 11-Jan-15 4:15am
   
Thanks
Rate this:
Please Sign up or sign in to vote.

Solution 5

SELECT ROW_NUMBER()  OVER (ORDER BY  ColumnName1) As SrNo, ColumnName1,  ColumnName2,ColumnName3 FROM  TableName
   
Comments
Richard Deeming 14-Jul-17 9:41am
   
Stop resurrecting ancient questions to re-post other people's answers!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100