Mysql – how to sort by timestamp field while counting duplicates

countMySQLsorting

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

Best Answer

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.

Related Solutions

Mysql – Should I use select count(*) for counting records or having a field like comments_no for tables like post, comments

How frequent do you need to perform this query?

If it is very rarely, then, perhaps it doesn't matter. And you could do "SELECT COUNT(1) FROM ...", do no need to use "SELECT COUNT(id)". SELECT COUNT(id) is not necessary in your case. Please refer here for more about when to use SELECT COUNT(1) etc.
As pointed out by Justin Cave above, does it need to be accurate all the time?

If you need it to be accurate, you might have to suffer the cost of the query.

If not, perhaps you could consider caching the value and only perform the query once every time period (once per 5 seconds?)

Mysql – Best way to set up database tables

You have no need to add a "modules" field to your main/users table. Just JOIN on the tables -- that's what Relational Databases are for. INNER or OUTER joins should be used depending on your needs.

For example, this will return a user with their conditions. If the user doesn't have any conditions, it will return NULL for those fields, but it will still return the user:

SELECT * 
FROM User U
   LEFT JOIN Conditions C ON U.Id = C.User_ID

Good visual representation of joins

Best Answer

Related Solutions

Mysql – Should I use select count(*) for counting records or having a field like comments_no for tables like post, comments

Mysql – Best way to set up database tables

Related Question