Re: Counting down

[Quoted] On 22.08.2017 23:45, Jerry Stuckle wrote:
> The order of rows in a table is unspecified unless you use the ORDER BY
> clause. And you CAN specify a key like you indicated - but only if the
> keys are contiguous - not something that is a good idea, because
> deleting a single row can cause the wrong row to be returned.

So what? The TO very explicitly postulated a situation where there is a number if rows with a contiguous primary key. Based on that assumption [1] the very first answer is correct. Period.

> And ORDER BY ... DESC isn't as slow as you think. Since there is an
> index on the ORDER BY column (the primary key), MySQL can go X number of
> records back in the index - which would be even faster than searching
> for a specific key, even if it is a binary search.

Not all table engines in MySQL support backward scanning on an index. On top of that, scanning for the n-th. entry is *not* a binary search and in general has the very probably worse efficiency O(n) vs. O(log N) for a binary search.

[Quoted] [1] I am sure you remember the TO. He was not recognized for asking specifically ... err ... sensible questions. In fact most of his questions originated from the rather stupid end of the spectrum. So now now he gets a stupid answer to a stupid [2] question. So what?

[Quoted] [2] Oh, yes! Stupid questions *do* exist Received on Wed Aug 23 2017 - 01:13:32 CEST