取回每个ID的第二个最新日期，而不是最新的 [英] Get back the second latest date per ID instead of the latest

问题描述

所以我有一个与 SQlite 的问题。我有一个表结果。

So I have an issue with SQlite. I have a table results.

我现在想写一个查询，让我不是最新的，而是之后的那一行。让我们来看一个例子：

I want to write a query now that gives me back not the latest, but the row after that. Let's take a look on an example:

ID,searchID,hit,time
1,1,3,1-1-2008
1,1,8,1-1-2009
1,1,4,1-1-2010
1,2,9,1-1-2011
1,2,10,1-1-2009

我想收回一个时间/ searchID现在（最新）：

and I want to get back one time per searchID now (the pre-latest):

1,1,8,1-1-2009
1,2,10,1-1-2009

真的很容易做到最后时间

It is really easy to do it with the last time

SELECT searchID, hit, max(time)
FROM results
group BY searchID

但由于某些原因我需要最新的消息。

But I need the pre-latest for some reasons.

PS：这个我发现了

PS: this one I found What is the simplest SQL Query to find the second largest value? but was not able to apply for my case.

推荐答案

假设您有工作日期比较，您可以使用复合查询删除所有最大行，然后将其余组合：

Assuming that you have working date comparisons, you can either remove all the maximum rows with a compound query, then group over the rest:

SELECT searchID, hit, MAX(time)
FROM (SELECT searchID, hit, time
      FROM results
      EXCEPT
      SELECT searchID, hit, MAX(time)
      FROM results
      GROUP BY searchID)
GROUP BY searchID

或者您可以在分组之前检查该时间不是该组中最大的时间：

or you can check, before the grouping, that the time is not the largest time in the group:

SELECT searchID, hit, MAX(time)
FROM results
WHERE time < (SELECT MAX(time)
              FROM results AS r2
              WHERE r2.searchID = results.searchID)
GROUP BY searchID

这篇关于取回每个ID的第二个最新日期，而不是最新的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！