2

I have a table with username, timestamp and action columns. I need to retrieve a list of usernames, total occurrences of each user and the latest timestamp for each user. The following statement works but it doesn't sort the timestamp field. I've tried adding "ORDER BY time DESC" but it has no effect. How can I retrieve the latest timestamp? The statement below returns the first occurrence of the timestamp.

user|time|action
jones|1-1-2016|actionA
jones|1-2-2016|actionA
jones|2-1-2016|actionB

SELECT user, time, COUNT(*) AS total 
FROM table 
GROUP BY user 
HAVING total > 0

Expected Results:

jones|2-1-2016|3
1
  • The HAVING total > 0 is useless. COUNT(*) can never be 0 in a simple query like this (only in a correlated subquery it could) – ypercubeᵀᴹ Nov 9 '16 at 23:32
1

Simply apply a MAX:

SELECT user, MAX(time), COUNT(*) AS total 
FROM table 
GROUP BY user 
ORDER BY MAX(time) DESC

Of course, this assumes that time is actually a datetime or timestampand not a VarChar.

2
  • And be sure to use the DATE datatype, not VARCHAR, for time. – Rick James Nov 12 '16 at 4:57
  • @RickJames: As the OP talked about a timestamp field I assumed he's using a correct datatype :) – dnoeth Nov 12 '16 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.