How do I select rows distinct on test_id with max

问题:

I am sorry if the title is a bit confusing but here is my db table:

id  test_id   result    test_timestamp
1   5         1         2014-10-21 09:22:59
2   5         2         2014-10-21 09:25:12
3   7         3         2014-10-21 09:23:10
4   7         1         2014-10-21 09:19:51
5   7         2         2014-10-21 09:20:00

the thing I want to do is select distinct test_id with the latest timestamp and it's result, so the result would be this:

id  test_id   result    test_timestamp
2   5         2         2014-10-21 09:25:12
3   7         3         2014-10-21 09:23:10

I have no problem getting the test_id and timestamp

SELECT distinct(method_id), max(test_timestamp) from results  group by method_id

but when I want the result with it I get all the possible results with it's latest timestamp for the test_id, I know why I get this but i'm not sure how to solve it.

SELECT distinct(method_id), max(test_timestamp), result from results  group by method_id, result

回答1:

For PostgreSQL, DISTINCT ON extension is the fastest and simplest way to do this:

SELECT DISTINCT ON (test_id)
       id, test_id, result, test_timestamp
  FROM results
 ORDER BY test_id, test_timestamp DESC, result;

回答2:

If you do not want to use PostgreSQLs special distinct on syntax, here is the simple filter way:

SELECT  
id, test_id, result, test_timestamp
FROM results
where test_timestamp = (select max(test_timestamp) from results r2 where r2.test_id=results.test_id) 

http://sqlfiddle.com/#!15/f5be6/2/0

The difference appears, if one has two rows for one test_id with the same maximum test_timestamp. Then this SQL delivers all rows for this maximum timestamp while distinct on only delivers the first one.