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?

Answer 1

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

Answer 2

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