mysql sub query with inner join?

Question

I'm trying to select the 10 last rows from my table messages. I'm selecting the name and last name too from table users using inner join.

The thing is I need this rows in ascendant order, so I'm trying to use a subquery as this post accepted answer.

SELECT * FROM (
  SELECT me.id, me.message, us.name1, us.lname1, SUBSTRING(us.lname2,1,1)
  FROM messages me INNER JOIN users us on me.rut=us.rut
  ORDER BY me.id DESC LIMIT 10
) tmp ORDER BY tmp.me.id ASC;

But it doesn't work, I actually don't know what's the proper way to do this with inner join.

Anyways, how can I make it work?

note: The inside parentesis query is working, it's just the outside parentesis query that doesn't work.

Answer 1

In the outer query you will only see a tmp.id and not a tmp.me.id. So your oder clause should be

ORDER BY id

(As the tmp.id is the only one you can leave the tmp. away and ORDER BY implicitly uses ASC.)