Adding row numbers to an SQL select result set

row_number()

I have always loved SQL as a language, it’s so elegant – most of the time anyway. Since SQL Server 2008 there has been a new function that returns a row number added to an sql select result set that I didn’t discover until very recently.

It came about because I was writing a new sql select query to list all the active sessions for our Syteline ERP system and I needed a quick way to determine the count (within the confines of the reporting system that I have developed). After some swift googling (not really sure where my career would be without Google) I discovered this little nugget. The query to get the list of active sessions is simple enough:

select UserName from connectionInformation where UserName not like ‘$service’

and it will return a list of usernames; but by adding the following you get a convenient row number

select row_number() OVER (ORDER BY UserName) AS Count,

UserName from connectionInformation where UserName not like ‘$service’

 The next problem for me is that our Syteline licenses are shared over two sites, one in the UK and one in Thailand, each with its own database. After a bit of experimentation I found that you could achieve row numbers for a unioned query like so.

select row_number() OVER (ORDER BY UserName) AS [Count], * from

(select

UserName

from connectionInformation where UserName not like ‘$service’

union

select

UserName

from otherDB.dbo.connectionInformation where UserName not like ‘$service’

)  as [UnionTable]

I don’t believe this function is part of the standard ANSI SQL so it will only work with Microsoft SQL database (I said 2008 above but it may be in 2005). If you are using another variety of SQL the same could be achieved a variety of (arguably)  less elegant ways. I don’t actually have any other databases installed but conceptually you would set a variable then increment it in the select statement, or maybe join to a derived subquery.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.