The forums ran from 2008-2020 and are now closed and viewable here as an archive.
Home › Forums › Other › Mysql – find row number in table with inconsistent IDs
Let’s say we have a table with inconsistent ID’s (some rows were deleted along the way or something of that nature).
So we have IDS: 1, 3, 4, 5, 7, 8, 10
Let’s say we want to return the id 8, and it’s row number in the table. Is this possible in mysql since it has no ROW_NUMBER() function?
All examples I find simply assume that all IDs are perfectly sequential (unless I misunderstand them).
An `id` is **not** the same thing as a “row number.” Nothing you do in a database should depend on the position in the table it is saved in.
If you *need* a continuous, ordinal index for your data, you could do something like:
UPDATE `mytable`, (SELECT @id := 0) s SET `id` = (@id := @id + 1)
However, that would get very expensive if you needed to do it with any regularity on a large table. There is probably a better solution to what you want to do – can you explain your objective?