Row numbers over partitions in SQL

Thank you for wanting to avoid the evil cursor. You will like this solution because of its elegance and simplicity.

select empid
     , row_number() 
          over ( partition by empid 
                 order by empid  ) 
           as seqno
 from yourtable

The ROW_NUMBER function provides the sequence number, which is assigned the column alias seqno.

The OVER clause specifies how the row number is calculated. PARTITION BY is used to create "windows" of rows, and ORDER BY defines the sequence of row numbering.

empid     seqno
- - - - - - - 
135142     1  
135142     2
135142     3
135142     4
- - - - - - - 
135147     1
135147     2
- - - - - - - 
135150     1  
135150     2
135150     3
- - - - - - - 

In effect, the row numbering "restarts" for every partition or window.