Get help. Give help. A Web Design and Development Community.

Mysql – find row number in table with inconsistent IDs

  • # June 6, 2013 at 10:44 am

    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).

    # June 6, 2013 at 2:04 pm

    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)

You must be logged in to reply to this topic.

We have a pretty good* newsletter.