Will the Numbering Be Safe in Multiple Variable Access

I am trying to shift the numbering of rows in my SELECT section to the server side.

I am doing the following:

SET @a=0;

SELECT @a:=@a+1, id
       /*, other fields*/
FROM tmytable 
WHERE (id >10) AND (id!= 73) /*etc.*/
order by id desc;

​x
 
SET @a=0;​SELECT @a:=@a+1, id       /*, other fields*/FROM tmytable WHERE (id >10) AND (id!= 73) /*etc.*/order by id desc;​

The code gives me what I want: the rows are properly numbered despite of WHERE clause, ordering and other circumstances. However I wonder how to make the statement safer. I assume that the concurrent sessions might address the same database variable simultaneously and the numbering will start from some undefined value of @a different from 0 + 1. I would prefer to exclude transactions and table locking as I need read only access and table locking might slow down the DB performance. Is there a way to declare and insert a variable within the SELECT statement?

Answer

Don’t use variables. Use row_number():

SELECT row_number() over (order by id desc), id
       #, other fields
FROM tmytable 
WHERE (id > 10) AND (id <> 73) #etc.
ORDER BY id DESC;

 
SELECT row_number() over (order by id desc), id       #, other fieldsFROM tmytable WHERE (id > 10) AND (id <> 73) #etc.ORDER BY id DESC;​

I’m not sure what you mean by “safe”. This is a SQL query and it will be run as a unit, independently of other sessions (under most circumstances).

Advertisement

Answer