mysql

[blakjack]

i have a table that is basically userids/names and points. displaying the top 5 by points is easy (order by points desc limit 5), but i also want to separately display the 5 surrounding yourself. So if you were in position 53, it would show the top 5, then separately it would show positions 50 through 55 with you at the center.

Code: Select all

A 350     L 200
B 349     M 195
C 348     N 190  <--- you are here
D 347     O 175
E 346     P 150

            ^^ i want this

i want to do this all in sql so that i dont have to return a full result set and traverse it to find your position. Im sure there is a way using variables and sub queries of sorts but Im not experienced with this. any ideas...even if it isnt in mysql.

[Fender]

A union might work. Haven't tested this SQL, just typing and thinking

Code: Select all

select *
from table
where points > 
  (select points from table where id = $myid)
order by points desc limit 2
union
select *
from table
where points <= 
  (select points from table where id = $myid)
order by points desc limit 3

Should be enough to get you started.

[Fender]

Just got this to work:

Code: Select all

(select *
from test
where points >= (select points from test where id = 5)
order by points asc limit 3)
union
(select *
from test
where points < (select points from test where id = 5)
order by points desc limit 2)
order by points desc

[blakjack]

thanks i'll give it a try

[blakjack]

damnit, of course the server is running MySQL 4.0.22 which does not support subqueries. and i do not control the server. groan.
ill see if i cant get it updated.

edit: nah bugger it, i'll just run 2 queries. works fine, apart from if there are a number of users on the same points...theres no guarantee that the user in question will get returned. I'll look into this further, at least its working now. If only mysql had 'with ties' functionality :\

Thanks.

[Foo]

Erm. I posted a reply here last night but it seems to have disappeared.

Basically, you could do this:

SELECT * FROM table WHERE rowid >= (usersid-2) LIMIT 5

If your userid was between 0 and 2 though you may get unexpected lines returned, or only 3 lines. Likewise if it's the last userid in the table.

[Tormentius]

Erm. I posted a reply here last night but it seems to have disappeared.

:icon6: What time? I haven't deleted anything an Ax is out of town as far as I know so...

[Foo]

About 7:00PM GMT.

very odd. Cheers for the interest though. Have messages been dissapearing?

[Tormentius]

About 7:00PM GMT.

very odd. Cheers for the interest though. Have messages been dissapearing?

Not AFAIK...

[blakjack]

fyi i just added another union to ensure all tied points were displayed, so it might display more than 5, but thats fine. basically:

Code: Select all

(select *
from test
where points = (select points from test where id = 5)
order by points asc)
union
(select *
from test
where points > (select points from test where id = 5)
order by points asc limit 2)
union
(select *
from test
where points < (select points from test where id = 5)
order by points desc limit 2)
order by points desc

[Fender]

You don't need the order by clause on the first select since all have the same points.

Code: Select all

(select *
from test
where points = (select points from test where id = 5))
union
(select *
from test
where points > (select points from test where id = 5)
order by points asc limit 2)
union
(select *
from test
where points < (select points from test where id = 5)
order by points desc limit 2)
order by points desc

[Foo]

Not to be an ass, but what part of my one-line select statement doesn't do what you needed?

[Fender]

That just returns the couple of users around the desired user by userid, not by points.

[Foo]

You just have to switch 'where userid >' for where points >'

[blakjack]

hmm? sorry foo i dont understand.
but i think what you suggested wouldnt be enough because neither userid's nor points are necessarily sequential.
here's a dump of current data:

Code: Select all

userid	points
3          3
4          4
9          4
17         9
19         1
21         5
29        10
30         7
42         4
58         5
83         2
85         8
101        2
104        2
107        2
110        1
111        2
112        3
113        4
114        3