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

  • This topic is empty.
Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
  • #45335

    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?

Viewing 2 posts - 1 through 2 (of 2 total)
  • The forum ‘Other’ is closed to new topics and replies.