mysql limit with desc

Anon

I am having major problems getting this work

I need to pull 5 records from a table sorted by id but in desc order, the desc order would appear to be producing the problem

Here is what I mean

("select * from table order by id desc limit 6,5")

You would think that would produce record 7-11, but it produces record 5 -1, so using that you would think that

limit 12,5 would produce records 11-7, but it shows no records as I have only 11 records, so I tried
limit 10,5

but that shows row 1

I am totally confused, if I remove the desc I have no problems, but this is causing havoc.

Any help appreciated.

[deleted]

From the MySQL manual:
"If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table."

Which is nice....

Anon

Hi sorry I thought I understood this until I tried it.

I need to display the table contents in reverse order I am trying to build a <<prev 123 next>> output.

But I cannot make sense of this - this is what I have tried and my outputs

select * from table order by id desc limit 6,5

displays
5
4
3
2
1

Thats perfect

but when u change it to

select * from table order by id desc limit 7,5

displays

4
3
2
1

thats not right

then change limit to 8,5

displays

3
2
1

Now my understanding is that it stops sorting when it get to the offset or first # after limit, so when it gets to row 8 it should display 5 rows desc (descending) my reading is that should be
7
6
5
4
3

but it is display 4-3-2-1

what am I missing, remove the desc and it works but that is not the output that I need

Sorry maybe I'm being stupid I'm tired been working on this for a few days and seem to be going backwards

Thanks for your help it really is appreciated

(Person with little hair scratched away)

Anon

Sorry Folks

Cracked it

It counts from the top down ahhhhhhhhhhhh

so with 11 rows row 0 is row 11 when u use desc

Sorry and thanks

[deleted]

oink 🙂

Anon

You're misreading the manual about LIMIT. When you supply two numbers, they are OFFSET, ROWS. That is, the first number says to start n rows into the result set. This takes precedence over the ROWS argument--i.e. when you say LIMIT 8,5 and there are only 11 rows total, it will indeed show just 3 rows (rather than backing up a few rows before row 8.)

In your case, if all you want is the first n rows, say it this way:

select * from table order by something limit 5;