CODEWITHSUNDEEP
mysqlsql
Shane
Shane

Reputation: 4983

How do I make a sql sentence to do order by before distinct?

For example, I have this order table, and it has columns: order_id, user_id, create_time, city_id.

Now I want to get the entry of an user's most recent order so basically what I want to do is:

select distinct(order.user_id), city_id 
from order 
where city_id != 0 
order by create_time desc

But as far as I know distinct will run before order by, which means there's already only one user_id left for each user before it reaches order by, so what do I do to make order by run first?

Upvotes: 0

Views: 49

Answers (1)

jarlh
jarlh

Reputation: 44766

Have a sub-query that returns each user's most recent create_time. JOIN with that result.

select o1.user_id, o1.city_id 
from order o1
join (select user_id, max(create_time) as newest_create_time
      from order
      where city_id != 0
      group by user_id) o2
    on o1.user_id = o2.user_id and o1.create_time = o2.newest_create_time
where o1.city_id != 0

Upvotes: 1

Related Questions