Q
Problem solve Get help with specific problems with your technologies, process and projects.

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.


This was last published in October 2007

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close