Tarlen Tarlen - 4 months ago 7
SQL Question

Selecting timeranges based on insertion date of matched result

I have a

messages(id, inserted_at)
table

I want to select the N most recent messages whose inserted_at column is with say, 2 minutes of the single most recent message.

Is this possible?

Answer

You could do that with a sub select in the where clause:

select  *
from    messages
where   inserted_at >=
        ( select max(inserted_at) - interval '90 minute'
          from   messages
        )
order by inserted_at desc
limit   2

... and just specify the interval of your choice, and the limit value.

Note that the two conditions (record limit N, date limit) are in competition, and you may get fewer records than N, or else get some messages excluded although they are within the date/time limit.

See SQL fiddle

If you meant that the date/time condition was to be a minimum time difference, then turn around the where condition from >= to <=:

select  *
from    messages
where   inserted_at <=
        ( select max(inserted_at) - interval '90 minute'
          from   messages
        )
order by inserted_at desc
limit   2
Comments