CODEWITHSUNDEEP
sqlpostgresql
Danny Dyla
Danny Dyla

Reputation: 691

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

Upvotes: 0

Views: 56

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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

Upvotes: 4

J Greene
J Greene

Reputation: 361

You can use ROW_NUMBER ... something like this

Select
    ROW_NUMBER() over (paritition by user order by id) as 'mrnk'
    ,id
    ,user
    ,message
From table
Where user = 'dan'
and mrnk <= 100

Or whatever number of Dan's messages you are looking for !

Upvotes: 0

Related Questions