Row numbers over partitions in SQL

I want to display the empid with seqno. My table would be:

empid
135142
135142
135142
135142
135147
135147
135150
135150
135150

I need the result as

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

Kindly help me to fetch the details instead of using cursor...

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.