RowNumber using SQL Query

Hi All,
I have following table structure,

----------------------
ChallanID ProductID PublicationDate Description Qty Amt
----------------------
43 9 4/1/2006 ABC 1 880
43 10 5/1/2006 BCA 1 930
43 11 5/1/2006 CBA 1 230

I want a sql query which select all the record with a serial number eg:

---------------------
SN# ChallanID ProductID PublicationDate Description Qty Amt
----------------------
1 43 9 4/1/2006 ABC 1 880
2 43 10 5/1/2006 BCA 1 930
3 43 11 5/1/2006 CBA 1 230use a temp table with identity column and insert your result into the temp table and select it back.|||U havent mentioned about how records to be ordered? In what order u want to generate serial No:?|||temp table shemp table...

SELECT count(*) as [SS#],a.LastName
FROM Employees a join
Employees b
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName

ps. I got this example from somewhere and it is not original work. If the original author sees this and takes any offense I am will to erase from the forum.|||SELECT count(*) as [SS#],a.LastName
FROM Employees a join
Employees b
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName
No, that only works if you use a unique key i.e.
select id=1,name='ccc' into #t1 union all
select 3,'bbb' union all
select 4,'aaa' union all
select 9,'bbb'

select count(*) as [ss#], name=min(a.name)
from #t1 a, #t1 b
where a.id>=b.id
group by a.id
order by 1
else use a temp table with identity column as suggested by khtan
select ss#=identity(int,1,1),name into #t2 from #t1 order by name
select * from #t2|||Problem is, either way you have no guarantee that the "serial number" for any record won't change as the contents of the table changes. Seems to me a "serial number" is expected to be static, so you really should add it as a permanent column to your table (perhaps as an identity datatype).