SQL to add an incrementing counter in results

One thing I alwys forget how to do is to add a sequential number to a query output in SQL.  Using the rank function you can get this.  A  query such as


select rank() OVER (ORDER BY accountid) as xrank, account, accountid from account

Will return the accountid and a sequential number based on the AccountID sort order.

SQL Rank Output

Similarly you can do something like this to sequence the results based on a sort on account names:

select rank() OVER (ORDER BY account) as xrank, account, accountid from account

SQL Rank Output 2



Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!