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.

Probably you need ORDER BY tmp.id ASC; instead of ORDER BY tmp.me.id ASC; — Oto Shavadze
– Oto Shavadze, Commented Jul 2, 2017 at 6:01

Carsten Massmann · Accepted Answer · 2017-07-02 06:04:14Z

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.)

edited Jul 2, 2017 at 6:04

answered Jul 2, 2017 at 6:01

Carsten Massmann

28.4k3 gold badges25 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql sub query with inner join?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related