Serial No in the Sql Query

[itsmeyogi]

Hi there ,

I am in a process of creating a report using MS Datareport.
I have written an Sql query. i want the serial no along with each row the query returns.

eg. select empno,name,sal from emp

1. E001 sam 100
2. E002 yogi 200
3. E003 soni 3000


etc.

Thanx in advance

yogitha

 

[tlbroadbent]

Do you mean that you want a row number on each row returned? SQL Server doesn't provide a RowNumber function. If that is what you seek, try the following.

select
empno, name, sal, RowNum=
(Select count(*) From emp
Where EmpNo<=e.EmpNo)
from emp e

The query above is not very effcient, especially for large tables.

Another method you can use that is more efficient for large tables is to create a temporary table with an Identity column, insert rows into the temp table and then select the rows.

Create table #tmpEmp
(empno char(6), name varchar(20),
sal decimal(10,2), RowNum int identity)

Set nocount On

Insert #tmpEmp(empno, name, sal)
Select empno,name,sal from emp

Select * From #tmpEmp Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.