Danny Dyla Danny Dyla - 6 months ago 11
SQL Question

Selecting a range based on a where clause

With a table like this

id | user | message
___________________
0 | dan | ...
1 | john | ...
2 | dan | ...
3 | dan | ...
4 | john | ...
(and so on)


How can I select dan's messages from 100 messages before the id to 100 messages after? If I just select based on the id column +/- 100 then there is not guaranteed to be 100 on either side because other users's messages may get in the way. I'd like to
SELECT * from table WHERE user = 'dan' and (some clause here) order by id;


Database is Postgresql

Answer

Hmmm . . . How about union all:

(select m.*
 from messages m
 where user = 'dan' and id < $id
 order by id desc
 limit 100
) union all
(select m.*
 from messages m
 where user = 'dan' and id > $id
 order by id asc
 limit 100
)
order by id;

This should even be reasonably efficient with an index on (user, id).

Comments