Row numbers over partitions in SQL

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin?

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.