MySQL negative limit?

Question

I am doing easy web based chat so I need to get (for example) 30 latest messages from a MySQL database. That doesn't make any problem but how can I take the data in up side down order? I mean, the oldest message is the first. I have to take latest 30 records by added time.

EDIT: Thanks for the answers, but...

ORDER BY added DESC LIMIT 30

gives:

15, 14, 13, 12, 11

...and I need:

11, 12, 13, 14, 15

I know I can get it using a subquery. Is there any better method?

Answer 1

Assuming you have a column called MessageTimestamp that holds a timestamp of each message:

SELECT [...] FROM MyChatTable ORDER BY MessageTimestamp DESC LIMIT 30

Answer 2

I don't see any way to do it without the sub-query, but it should not be a problem. The outer ORDER BY sorts only 30 rows, so performance loss of that will be negligible.

SELECT * FROM
(
  SELECT *
  FROM posts
  ORDER BY post_time DESC
  LIMIT 0, 30
) x
ORDER BY post_time ASC

Of course you should use the actual culumn-names instead of *.

Answer 3

As mentioned above, a simple "desc" sort gets the oldest 30 messages, not the newest (though they are in the right order).

Actually all you need is:

(select col from table order by added desc limit 30) order by added asc

I hope this helps.