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;
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?
Advertisement
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;
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).