2

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

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: 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.

Community
  • 1
  • 1
kwoxer
  • 3,734
  • 4
  • 40
  • 70
  • Define 'latest'. And note that dates and times in SQL generally adhere to a specific format – Strawberry Oct 30 '14 at 20:27
  • Wow guys, the problem is, that I just have time to test it the coming Monday. But thank you in advance. Your idea looking very nice =) – kwoxer Oct 31 '14 at 08:23

1 Answers1

1

Using any other day/month than 1/1 will foul up the date string comparisons; you should use a date format like yyyy-mm-dd instead.

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
CL.
  • 173,858
  • 17
  • 217
  • 259
  • @Strawberry No, SQLite [guarantees](http://www.sqlite.org/releaselog/3_7_11.html) that unaggregated columns come from the same row that matches the MAX(). – CL. Oct 31 '14 at 10:28
  • Oops - didn't see the RDBMS - and I didn;t know that! An sqlfiddle (sql.js) might be useful. – Strawberry Oct 31 '14 at 11:03
  • Cool, working as aspected. Just giving back ID's that have a minimum of 2 records. And getting back the 2nd latest results with these both Querys. The 2nd query of you is 9ms while the first is 10 ms. Because the 2nd is shorter and faster, I'll take it. Thank you very much. – kwoxer Nov 03 '14 at 08:00