Select to give ordinal number of values in another column 1

[NickW2]

If you have a table, say, with 2 columns
Nominal number (N1,N2,...Nn)
Casualty? (Y/N)
Is there an easy way of selecting (deriving) an (ordinal) casualty number, which is not stored as a column.

e.g. if you had the following data

N1 Y
N2 N
N3 Y
N4 Y
N5 N

selecting casualties only and also a corresponding casualty number, would give

N1 1
N3 2
N4 3

 

[nigelrivett]

select col1 ,
(select count(*) from tbl t2 where t2.col2 = 'Y' and t2.col1 <= t1.col1)
from tbl t1
where col2 = 'Y'

Or you can put them into a temp table with an identity and select the output from that.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.