How can I select the most recent input for each member?

Question

Assume I have a MySQL table:

╔══════╦═════╦═════════╦═════════╗
║ time ║ mid ║ field_1 ║ field_2 ║
╠══════╬═════╬═════════╬═════════╣
║  100 ║   1 ║      32 ║      54 ║
║  100 ║   2 ║       0 ║      34 ║
║  100 ║   3 ║      44 ║      99 ║
║  200 ║   1 ║       0 ║      45 ║
║  200 ║   2 ║       0 ║      45 ║
║  200 ║   3 ║       4 ║      59 ║
║  200 ║   4 ║      45 ║      45 ║
╚══════╩═════╩═════════╩═════════╝

time is a UNIX timestamp. mid is the member id. field_1 is supplied by the member. field_2 is filled automatically.

I wish to select the row with the most recent non-zero field_1 for all members. So the query would result in:

╔══════╦═════╦═════════╦═════════╗
║ time ║ mid ║ field_1 ║ field_2 ║
╠══════╬═════╬═════════╬═════════╣
║  100 ║   1 ║      32 ║      54 ║
║  200 ║   3 ║       4 ║      59 ║
║  200 ║   4 ║      45 ║      45 ║
╚══════╩═════╩═════════╩═════════╝

The only solution I came up with does not seem very elegant:

SELECT *
FROM (
    SELECT *
    FROM t1
    WHERE field_1 > 0
    ORDER BY time DESC
) AS a
GROUP BY mid

Is there a better way?

Try to learn about [MySQL JOIN](http://dev.mysql.com/doc/refman/5.0/en/join.html) — Ron van der Heijden, Oct 26 '12 at 15:03
http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group/ — newtover, Oct 26 '12 at 15:03

John Woo · Answer 1 · 2012-10-26T15:01:40.770

4

The idea here is to create a subquery which gets the max time for each mid and join it on the table itself.

SELECT a.*
FROM tableName a
INNER JOIN
(
SELECT mid, MAX(time) maxV
FROM tableName
WHERE field_1 > 0
GROUP BY mid
) b ON a.mid = b.mid and
        a.time = b.maxV

SQLFiddle Demo

edited Oct 26 '12 at 15:01

answered Oct 26 '12 at 14:56

John Woo

258,903
69
498
492

score 1 · Accepted Answer · answered Oct 26 '12 at 14:59

1

SELECT a.*
FROM t1 AS a
INNER JOIN
(
    SELECT mid, MAX(time) AS maxTime
    FROM t1
    WHERE field_1 <> 0
    GROUP BY mid
) b ON a.mid = b.mid AND a.time = b.maxTime

SQL Fiddle

answered Oct 26 '12 at 14:59

lc.

113,939
20
158
187

How can I select the most recent input for each member?

2 Answers2

SQLFiddle Demo

Linked