MySQL order by with count and group by issues -

my tables like:

# table user  user_id pk ...  # table buy  buy_id pk user_id fk ...  # table offert  offert_id user_id ... 

well need know last 'buy' of 1 'user' , count of 'offert' 'user' has, tried like:

select b.buy_id,count(distinct c.offert_id) cv user inner join buy b using(user_id) left join offert c using(user_id) a.user_id=4 group a.user_id order b.buy_id desc 

but returns first 'buy' not last, order doesn't make effect

i know can sub queries know if there way whout use sub queries, maybe using max functions idk how it.

thanks.

your approach not guaranteed work. 1 big reason group by processed before order by.

assuming mean biggest buy_id each user, can as:

select u.user_id, u.last_buy_id, count(distinct o.offert_id) (select u.*,              (select buy_id buy b u.user_id = u.user_id order buy_id desc limit 1              ) last_buy_id       user u      ) left outer join      offert o      on o.user_id = u.user_id group u.user_id; 

the first subquery uses correlated subquery last buy id each user. joins in offert , aggregation. note version includes user_id in aggregation.