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

Question

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?

Not really. Rows in an RDBMS represent unordered sets. So without an ORDER BY clause, the '2' has no meaning. When you create the array from the query result, your application code arbitrarily assigns an order to the result, but you cannot reasonably deduce the order of the data set from the order of the array, because, as mentioned, there is no order. Makes sense? — Strawberry, Sep 02 '20 at 14:48
If you want `B` as [2], and more generally the nth row from an alphabetical order, add an index on `key1` (if not already) and `ORDER BY key1`. If you want an absolute (always the same `B` => 2, even if `AA` is added, add an `auto_increment` col, and order by that. — Déjà vu, Sep 02 '20 at 14:54
That query wont give that result... So we are kinda stumbling about in the dark here. You have not made it clear what you are actually trying to do and what the table actually looks like — RiggsFolly, Sep 02 '20 at 15:08
What version of MySQL are you using, but basically you can see how in [this tutorial](https://www.mysqltutorial.org/mysql-row_number/) — RiggsFolly, Sep 02 '20 at 15:40
See https://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select — Dharman, Sep 02 '20 at 16:01

score 1 · Accepted Answer · answered Sep 02 '20 at 16:05

1

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

answered Sep 02 '20 at 16:05

Dharman

1 Answers1