如何选择带有最大时间戳和结果的test_id上不同的行 [英] How do I select rows distinct on test_id with max timestamp and result

问题描述

很抱歉，标题有点令人困惑，但这是我的数据库表:

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

我要做的是选择带有最新时间戳的独特test_id及其结果，因此结果将是这样:

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

我没有问题可以获取test_id和时间戳

I have no problem getting the test_id and timestamp

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

但是当我想要得到结果时，我会得到所有可能的结果，它是test_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

推荐答案

对于PostgreSQL，

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;

这篇关于如何选择带有最大时间戳和结果的test_id上不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！