Page 1 of 1
mysql
Posted: Thu Mar 10, 2005 12:57 am
by 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.
Posted: Thu Mar 10, 2005 2:38 pm
by 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.
Posted: Thu Mar 10, 2005 3:05 pm
by 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
Posted: Thu Mar 10, 2005 11:54 pm
by blakjack
thanks i'll give it a try
Posted: Fri Mar 11, 2005 1:08 am
by 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.
Posted: Fri Mar 11, 2005 4:59 pm
by 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.
Posted: Fri Mar 11, 2005 5:58 pm
by Tormentius
Foo wrote: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...
Posted: Fri Mar 11, 2005 7:39 pm
by Foo
About 7:00PM GMT.
very odd. Cheers for the interest though. Have messages been dissapearing?
Posted: Sat Mar 12, 2005 4:17 am
by Tormentius
Foo wrote:About 7:00PM GMT.
very odd. Cheers for the interest though. Have messages been dissapearing?
Not AFAIK...
Posted: Sat Mar 12, 2005 12:10 pm
by 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
Posted: Sun Mar 13, 2005 12:34 am
by 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
Posted: Sun Mar 13, 2005 10:34 am
by Foo
Not to be an ass, but what part of my one-line select statement doesn't do what you needed?
Posted: Sun Mar 13, 2005 6:47 pm
by Fender
That just returns the couple of users around the desired user by userid, not by points.
Posted: Sun Mar 13, 2005 7:38 pm
by Foo
You just have to switch 'where userid >' for where points >'
Posted: Mon Mar 14, 2005 5:09 am
by 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