SQL按升序选择最后10行

Question

here's my current code:

$messages = mysqli_query($link, "SELECT `user`, `message` FROM `chat` ORDER BY `id` DESC LIMIT 10");
while($row = mysqli_fetch_array($messages)) {
    echo "<strong>".$row['user'].":</strong> ".safe_out($row['message'])."<br />";  
}

This prints the last 10 messages from the chat table in descending order. What I'm trying to do is print the last 10 messages in ascending order.

Changing DESC to ASC just prints out the first 10 messages, but I'm trying to get the last 10 messages to print in ascending order.

Do I need to put the mysqli_query results into an array and use reverse or is there an easier way?

Thanks

Answer 1

You can use a derived table to re-sort the last 10 messages in ascending order

SELECT * FROM (
  SELECT `id`, `user`, `message` FROM `chat` ORDER BY `id` DESC LIMIT 10
) t1 ORDER BY t1.id