Range Values

Krakken

I need to select like 10 or so values in the closest boundary to the user.

For example:
Imaging I have a boundary of 4.

Now, if the user is rank 1st it should return all users 2nd, 3rd, 4th and 5th. (None above so it returns the lower 4)
If the user is rank 4th it should return 1st, 2nd, 3rd and 5th (Gets all highest values first then starts with the lower values).
If the users rank is 120th it should return 116th, 117th, 118th and 119th (It can return 4 high values with no problem).

Is there a function or group of functions to do this?

barand

Something like this perhaps?

SELECT * FROM mytable WHERE (rank BETWEEN $myrank - 4 AND $myrank + 4) AND (rank <> $myrank) ORDER BY rank

Krakken

Thanks 🙂

Krakken

Ah, sorry I left something out. The user doesn't actually have a rank but instead a score. I need it to always return 4 values so "BETWEEN $lowscore AND $highscore" would be impracticle. any ideas?

barand

SELECT *, ABS($myscore-score) as diff FROM mytable ORDER BY diff LIMIT = 4

should give the 4 nearest. You might want to work on variations of this as an exercise for the reader🙂

Krakken

Hmm interesting. Could I trouble to ask how this query works? I can't quite figure it.

Krakken

I tried it and i'm afraid it isn't returning any data.

Krakken

No-one knows?

barand

I think

SELECT *, ABS($myscore-score) as diff FROM mytable ORDER BY diff LIMIT = 4

should be

SELECT *, ABS($myscore-score) as diff FROM mytable ORDER BY ABS($myscore-score) LIMIT = 4

I was subtracting the score in each record from $myscore. The 'ABS' bit makes the difference positive regardless of whether the score is higher or lower than myscore. The lowest numbers would be the nearest, higher or lower.

Krakken

Aha I see, i'll try the query in a min. Thanks again!

Krakken

Unfortunately again it won't work :/

Krakken

Anyone?

Weedpacket

What, apart from dropping the syntax-breaking = between LIMIT and 4? (mysql_error() would probably have pointed that out.)

And yes, ORDER BY diff would work.

Krakken

Thanks, it works now. Today I was thinking and have come up with a way of making the first one work which is more what I needed. I'll post if it works.