how to number rows in select statement

[itbnorris]

I am trying to number the rows I retrieve from a select statement but with a catch...

I am grouping my results, and the numbering must restart with each group... ie

Group A
1 Field1 Field2
2 Field1 Field2
Group B
1 Field1 Field2
2 Field1 Field2
3 Field1 Field2

Is there a way to do this in a select statement?

Thanks for your help everyone!

 

[sem]

Read about ROW_NUMBER function. An example:

SELECT grouping_field
,ROW_NUMBER() OVER (PARTITION BY grouping_field ORDER BY field1, field2) AS ord_no
, field1, field2
FROM my_table

Regards, Dima

http://seminihin.narod.ru