row count for each person

[sbcsandhu]

cwin is the unique identfier for a person,
with the query before if a person has 2 records i would get a 2 in column row_count, but how would i do this, if i want a 1 on the first record and then a 2 on the second record, currently since there are 2 records for the cwin, i get a 2 in both columns.
how would i do it, so i get a 1 in the 1st record and then a 2 in the 2nd record

select *
from appraisal_F f
inner join (select cwin, count(*) as a
from appraisal_F
group by cwin ) as row_count on d.cwin = f.cwin

 

[paulkeys]

You could look at using the row_number() function - books online has a couple of good examples

 

[xlbo]

RunningCount should also be available - you can set the context for it to number within in case you have different groupings etc

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question