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

 

ABOUT THE AUTHOR

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) news and product updates!

You have Successfully Subscribed!