Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
1.80/5 (2 votes)
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 11-Jan-21 17:51pm
v2

Try:
SQL
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS RowNum,* FROM myTable
 
Share this answer
 
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
SQL
SELECT ROW_NUMBER()  OVER (ORDER BY  ColumnName1) As SrNo, ColumnName1,  ColumnName2,ColumnName3 FROM  TableName
 
Share this answer
 
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, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900