CODEWITHSUNDEEP
mysql
ctown4life
ctown4life

Reputation: 965

mysql select most recent row by date for each user

I'm trying to select the most recent rows for every unique userid where pid = 50 and active = 1. I haven't been able to figure it out.

Here is a sample table

+-----+----------+-------+-----------------------+---------+
| id  | userid   | pid   | start_date            | active  |
+-----+----------+-------+-----------------------+---------+
| 1   | 4        | 50    | 2015-05-15 12:00:00   | 1       |
| 2   | 4        | 50    | 2015-05-16 12:00:00   | 1       |
| 3   | 4        | 50    | 2015-05-17 12:00:00   | 0       |
| 4   | 4        | 51    | 2015-06-29 12:00:00   | 1       |
| 5   | 4        | 51    | 2015-06-30 12:00:00   | 1       |
| 6   | 5        | 50    | 2015-07-05 12:00:00   | 1       |
| 7   | 5        | 50    | 2015-07-06 12:00:00   | 1       |
| 8   | 5        | 51    | 2015-07-08 12:00:00   | 1       |
+-----+----------+-------+-----------------------+---------+

Desired Result

+-----+----------+-------+-----------------------+---------+
| id  | userid   | pid   | start_date            | active  |
+-----+----------+-------+-----------------------+---------+
| 2   | 4        | 50    | 2015-05-16 12:00:00   | 1       |
| 7   | 5        | 50    | 2015-07-06 12:00:00   | 1       |
+-----+----------+-------+-----------------------+---------+

I've tried a bunch of things and this is the closest I got but unfortunately it is not quit there. 

SELECT *
FROM mytable t1
WHERE
    (
        SELECT COUNT(*)
        FROM mytable t2
        WHERE
            t1.userid = t2.userid           
            AND t1.start_date < t2.start_date
    ) < 1
AND pid = 50
AND active = 1
ORDER BY start_date DESC

Upvotes: 0

Views: 69

Answers (1)

amdixon
amdixon

Reputation: 3833

plan

  1. get last record grouping by userid where pid is 50 and is active
  2. inner join to mytable to get the record info associated with last

query

select 
my.*
from
(
select userid, pid, active, max(start_date) as lst
from mytable
where pid = 50
and   active = 1
group by userid, pid, active
) maxd
inner join mytable my
on  maxd.userid = my.userid
and maxd.pid    = my.pid
and maxd.active = my.active
and maxd.lst    = my.start_date
;

output

+----+--------+-----+------------------------+--------+
| id | userid | pid |       start_date       | active |
+----+--------+-----+------------------------+--------+
|  2 |      4 |  50 | May, 16 2015 12:00:00  |      1 |
|  7 |      5 |  50 | July, 06 2015 12:00:00 |      1 |
+----+--------+-----+------------------------+--------+

sqlfiddle

notes

as suggested by @Strawberry, updated to join also on pid and active. this will avoid the possibility of a record which is not active or not pid 50 but has exact same date also being rendered.

Upvotes: 2

Related Questions