Redis get member where score is between min and max

Question

I have a table in sql with 3 columns: BIGINT StartNumber, BIGINT EndNumber, BIGINT LocationId, and I need to be able to do something like this

Select LocationId where StartNumber < @number and EndNumber > @number.

for example:

StartNumber EndNumber LocationId
1             5          1
6             9          1
10            16         2

and when I have @number = 7 I should get LocationId = 1

How can I do this in redis?

I was thinking to move this table to redis, use sorted set and ZRANGEBYSCORE but it did't work for me: 1) When I am using ZADD key score member [score] [member], I am unable to add 2 elements with the same member and different score even with nx parameter: zadd myset nx 1 "17" 2 "17" - it will add one element and then update its score instead of adding two elements. 2) when I am adding this: zadd set1 2 "a" 4 "b" 6 "c" 10 "d" and then trying to do zrangebyscore set1 3 3 (want to get member whose score include 3) I em getting empty result

P.s. All commands are executed on the example pages of redis website.

Answer 1

So as I understood the task, you don't have overlaps and each interval maps to only one location (?) and intervals don't have gaps. Based on this you can use only one sorted list with lower (or upper) bound values:

ZADD StartNumber 1 "1:5:1" 6 "6:9:1" 10 "10:16:2"

Then you can use:

ZREVRANGEBYSCORE StartNumber 7 -inf LIMIT 0 1

And it will be O(log(N)).