Selecting the most recent row before a certain timestamp

Question

I have a table like this called tt

ID|Name|Date|Value|
------------------------------------
0|  S1| 2017-03-05 00:00:00|    1.5|
1|  S1| 2017-04-05 00:00:00|    1.2|
2|  S2| 2017-04-06 00:00:00|    1.2|
3|  S3| 2017-04-07 00:00:00|    1.1|
4|  S3| 2017-05-07 00:00:00|    1.2|

I need to select the row with the highest time for each Name that is < theTime

theTime being just a variable with the timestamp. In the example you could hardcode a date string, e.g. < DATE '2017-05-01' I will inject the value of the variable later programmatically with another language

I'm having a difficult time figuring out how to do this... does anyone know?

Also, I would like to know how to select what I described above but limited to a specific name, e.g. name='S3'

Answer 1

It would be nice if hsqldb really supported row_number():

select t.*
from (select tt.*,
             row_number() over (partition by name order by date desc) as seqnum
      from tt
      where  . . .
     ) t
where seqnum = 1;

Lacking that, use a group by and join:

select tt.*
from tt join
     (select name, max(date) as maxd
      from tt
      where date < THETIME
      group by name
     ) ttn
     on tt.name = ttn.name and tt.date = ttn.maxd;

Note: this will return duplicates if the maximum date has duplicates for a given name.

The where has the limitation on your timestamp.