I am writing a request for a sample of TOP-10 buyers. I could easily bring out buyers for all the time, but there was a question about sorting by date. I recorded the request in this way, everything is executed, but the UNIX sorting of the o.date column does not occur. Request:

SELECT o.user_id, SUM(o.total_price) as nsum, o.date as odate, u.id as uid, u.balance as balance, u.name as login FROM s_orders as o,s_users as u WHERE user_id!='NULL' AND o.user_id=u.id AND o.date >= UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)) GROUP BY user_id ORDER BY nsum DESC LIMIT 10
  • and where in this request sorting by column o.date ? Can meant that condition WHERE not fulfilled? - Anatol

1 answer 1

  1. to sort by o.date column, it must be explicitly specified in the ORDER BY part

  2. Now o.date is randomly selected from a group with the same user_id, since it is specified without group functions

I suppose that you want to get the last order date for each user, then use max (o.date) as odate

  • Can you show in code what you mean? The result is not obtained by making your edits. - user197085
  • make an example of test data on sqlfiddle.com and what should be the result on them. until you specify what you mean by sorting, you need to understand what exactly you need - retvizan