CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
TheBounder
TheBounder

Reputation: 39

Order multiple rows by field from most recent entry

I'm trying to return a list of users, ordered by a field (pt_seen) on the users most recent row.

Does that make sense?

So far I have: 

SELECT u.users_id, 
       u.username, 
       ed.date 
FROM users u
LEFT JOIN exercises_done ed 
    ON ed.user_id = u.users_id
WHERE u.pt_id = 1
GROUP BY u.users_id

Which obviously just returns the users grouped.

The tables look like this:

users:

users_id | pt_id | username
1        | 1     | billy
2        | 1     | bob
3        | 1     | sue


exercises_done:

exercises_done_id | user_id | pt_id | exercises_done_date | pt_seen
1                 | 1       | 1     | 2018-01-01          | 0
2                 | 1       | 1     | 2018-01-02          | 0
3                 | 1       | 1     | 2018-01-03          | 1
4                 | 2       | 1     | 2018-01-05          | 1
5                 | 3       | 1     | 2018-01-04          | 0

and I'm trying to get results like this:

users_id | username | exercises_done_date | pt_seen
1        | billy    | 2018-01-02          | 0
3        | sue      | 2018-01-04          | 0 
2        | bob      | 2018-01-05          | 1

The aim is that I show users at the top of the list who have a pt_seen value of 0, then ordered by exercises_done_date.

Any help would be great,

Upvotes: 3

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can select the most recent exercise in the where clause rather than using aggregation:

SELECT u.users_id, u.username, ed.*
FROM users u LEFT JOIN
     exercises_done ed
     ON ed.user_id = u.users_id
WHERE ed.exercises_done_date = (SELECT MAX(ed2.exercises_done_date)
                                FROM exercises_done ed2
                                WHERE ed2.user_id = ed.user_id
                               ) AND
      u.pt_id = 1
ORDER BY u.pt_seen, exercises_done_date DESC;

Upvotes: 2

flip
flip

Reputation: 555

You can add an ORDER BY clause after your GROUP BY.

SELECT u.users_id, u.username, ed.date FROM 
users u
LEFT JOIN exercises_done ed ON ed.user_id = u.users_id
WHERE
u.pt_id = 1
GROUP BY 
u.users_id
ORDER BY
pt_seen, exercises_done_date

Upvotes: 0

Related Questions