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).

Row Num in matrix

I am using Matrix in my report.
I have One Row Group (ProductID) and 7 Static rows.
Expression for my ProductID Group is =Fields!ProductID.Value
How can I set the Page break so I can accomodate 4 Row Groups (i.e) 28 lines
on one page and then goto next page.
Any Suggestions.Assuming you have one row of data in your query per product, you can put the
matrix in a list which groups on =Ceiling(RowNumber(Nothing)/4) and put
PageBreakAtEnd on either the list's grouping or on the matrix.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"battula" <battula_28@.hotmail.com> wrote in message
news:ODossYidEHA.1152@.TK2MSFTNGP09.phx.gbl...
> I am using Matrix in my report.
> I have One Row Group (ProductID) and 7 Static rows.
> Expression for my ProductID Group is =Fields!ProductID.Value
> How can I set the Page break so I can accomodate 4 Row Groups (i.e) 28
lines
> on one page and then goto next page.
> Any Suggestions.
>sql