Is there a way to get the row number of a record within the result of a query?

user_1236183 0
Viewed 55

Let's imagine we have a query like:

SELECT id,key1
FROM my_table
WHERE condition = true
ORDER BY id

And this gives as a result the following array:

[
    [id => 123, key1 => 'A'], 
    [id => 234, key1 => 'B'],
    [id => 546, key1 => 'C']
]

The array has 3 values. The index of the row with key1='B' is 1.

Is there a way to get the '1' directly from a query?

mysql
1 Answers

You can use ROW_NUMBER() OVER() to get the positional index of each row in the result set.

SELECT id, key1, ROW_NUMBER() OVER(ORDER BY id)
FROM my_table
WHERE condition = true
ORDER BY id
user_18394390
Related