stackoverflow的排序和限制建议对我不起作用

Question

I try to get the 10 latest entries from a table and at the end ordered ascending, so that the latest/newest entry is the last one I see.

This is my query so far:

SELECT `title`, `content`, `message_id`
FROM `a_message_table`
WHERE `some-id` = 126
AND (`user_init_id` = 30 OR `user_follow_id` = 30)
ORDER BY `message_id`  DESC
LIMIT 0, 10

As suggested as answer for this question: Select last 20 order by ascending - PHP/MySQL

I tried to modify my query like this:

(SELECT `title`, `content`, `message_id`
FROM `a_message_table`
WHERE `some-id` = 126
AND (`user_init_id` = 30 OR `user_follow_id` = 30)
ORDER BY `message_id`  DESC
LIMIT 0, 10)
ORDER BY `message_id` ASC

1.) Put the query into brackets

2.) add the line ORDER BY message_id ASC

Mysql reports the following errors:

1. This type of clause has been previously analyzed.(near "ORDER BY" at position 339)
2. unknown statement-type. (near "ORDER BY" at position 339)
3. #1054 - Unknown column 'message_id' in 'order clause'

I'm using mysql 5.7, mariaDB.

Someone can support here, to find an efficent way to get the latest ten entries of a table ordered so that the latest entry is the last one.

It is for a messaging system and I want that the user gets the latest entry when he scrolls down. When he scrolls up, he should get the next ten entries ordered like this. This is why I'm using LIMIT 0,10, later it says LIMIT 10, 10 and so on...

Answer 1

Use this:

Select * from 
(SELECT `title`, `content`, `message_id`
FROM `a_message_table`
WHERE `some-id` = 126
AND (`user_init_id` = 30 OR `user_follow_id` = 30)
ORDER BY `message_id`  DESC
LIMIT 0, 10) t
ORDER BY `t`.`message_id` ASC