CODEWITHSUNDEEP
mysqlsql
MamaWalter
MamaWalter

Reputation: 2113

sort on two parameters

I have a table with checkin records like this:

userID      checkin_date
   1    '2014-01-28 08:00:00'
   1    '2014-01-27 09:10:00'
   1    '2014-01-26 12:24:00'
   2    '2014-01-26 08:17:00'
   3    '2014-01-26 09:33:00'
   2    '2014-01-28 10:28:00'
   ..         .........

and i want with a single request sort the ten users who checkin the most since a specific date (order by nb visite DESC) (that easy) but for each one i also want to know the date of their last checkin.

i do something like this:

SELECT 
  userID, 
  count(*) as nbVisit,
  checkin_date
FROM(
SELECT 
 userID,
 checkin_date
 FROM checkin_table
 WHERE checkin_date > '2014-01-25'  
ORDER BY checkin_date DESC )as sub
 GROUP BY userID
ORDER BY nbVisit DESC
LIMIT 10

is it the best way to do it ? will it work in any time ? is it efficient with lots of data ?

SQLFIDDLE

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You don't need a subquery for this, just use max() along with count(*):

  SELECT userID, max(checkin_date), count(*) as nbVisit,
  FROM checkin_table
  WHERE checkin_date > '2014-01-25' 
  GROUP BY userId
  ORDER BY nbVisit desc
  LIMIT 10 ;

Upvotes: 2

Related Questions