Quake3World.com Forums
     Technology & Troubleshooting
        mysql
Post new topicReply to topic
Login | Profile | | FAQ | Search | IRC




Print view Previous topic | Next topic 
Topic Starter Topic: mysql
blakjack
The Illuminated
The Illuminated
Joined: 28 Mar 2000
Posts: 1142
PostPosted: 03-09-2005 04:57 PM           Profile Send private message  E-mail  Edit post Reply with quote


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:
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.
Top
                 
Fender
Jesus of Suburbia
Jesus of Suburbia
Joined: 14 Jan 2001
Posts: 12713
PostPosted: 03-10-2005 06:38 AM           Profile   Send private message  E-mail  Edit post Reply with quote


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

Code:
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. :shrug:
Top
                 
Fender
Jesus of Suburbia
Jesus of Suburbia
Joined: 14 Jan 2001
Posts: 12713
PostPosted: 03-10-2005 07:05 AM           Profile   Send private message  E-mail  Edit post Reply with quote


Just got this to work:
Code:
(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
Top
                 
blakjack
The Illuminated
The Illuminated
Joined: 28 Mar 2000
Posts: 1142
PostPosted: 03-10-2005 03:54 PM           Profile Send private message  E-mail  Edit post Reply with quote


thanks i'll give it a try
Top
                 
blakjack
The Illuminated
The Illuminated
Joined: 28 Mar 2000
Posts: 1142
PostPosted: 03-10-2005 05:08 PM           Profile Send private message  E-mail  Edit post Reply with quote


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.
Top
                 
Foo
Timed Out
Timed Out
Joined: 02 Aug 2000
Posts: 38064
PostPosted: 03-11-2005 08:59 AM           Profile   Send private message  E-mail  Edit post Reply with quote


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.



_________________
"Maybe you have some bird ideas. Maybe that’s the best you can do."
― Terry A. Davis
Top
                 
Tormentius
The voices in your head
The voices in your head
Joined: 14 Dec 2002
Posts: 10054
PostPosted: 03-11-2005 09:58 AM           Profile   Send private message  E-mail  Edit post Reply with quote


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...
Top
                 
Foo
Timed Out
Timed Out
Joined: 02 Aug 2000
Posts: 38064
PostPosted: 03-11-2005 11:39 AM           Profile   Send private message  E-mail  Edit post Reply with quote


About 7:00PM GMT.

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



_________________
"Maybe you have some bird ideas. Maybe that’s the best you can do."
― Terry A. Davis
Top
                 
Tormentius
The voices in your head
The voices in your head
Joined: 14 Dec 2002
Posts: 10054
PostPosted: 03-11-2005 08:17 PM           Profile   Send private message  E-mail  Edit post Reply with quote


Foo wrote:
About 7:00PM GMT.

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


Not AFAIK...
Top
                 
blakjack
The Illuminated
The Illuminated
Joined: 28 Mar 2000
Posts: 1142
PostPosted: 03-12-2005 04:10 AM           Profile Send private message  E-mail  Edit post Reply with quote


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 *
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
Top
                 
Fender
Jesus of Suburbia
Jesus of Suburbia
Joined: 14 Jan 2001
Posts: 12713
PostPosted: 03-12-2005 04:34 PM           Profile   Send private message  E-mail  Edit post Reply with quote


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

Code:
(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
Top
                 
Foo
Timed Out
Timed Out
Joined: 02 Aug 2000
Posts: 38064
PostPosted: 03-13-2005 02:34 AM           Profile   Send private message  E-mail  Edit post Reply with quote


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



_________________
"Maybe you have some bird ideas. Maybe that’s the best you can do."
― Terry A. Davis
Top
                 
Fender
Jesus of Suburbia
Jesus of Suburbia
Joined: 14 Jan 2001
Posts: 12713
PostPosted: 03-13-2005 10:47 AM           Profile   Send private message  E-mail  Edit post Reply with quote


That just returns the couple of users around the desired user by userid, not by points.
Top
                 
Foo
Timed Out
Timed Out
Joined: 02 Aug 2000
Posts: 38064
PostPosted: 03-13-2005 11:38 AM           Profile   Send private message  E-mail  Edit post Reply with quote


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



_________________
"Maybe you have some bird ideas. Maybe that’s the best you can do."
― Terry A. Davis
Top
                 
blakjack
The Illuminated
The Illuminated
Joined: 28 Mar 2000
Posts: 1142
PostPosted: 03-13-2005 09:09 PM           Profile Send private message  E-mail  Edit post Reply with quote


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:
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
Top
                 
Quake3World.com | Forum Index | Technology & Troubleshooting


Post new topic Reply to topic


cron
Quake3World.com
© ZeniMax. Zenimax, QUAKE III ARENA, Id Software and associated trademarks are trademarks of the ZeniMax group of companies. All rights reserved.
This is an unofficial fan website without any affiliation with or endorsement by ZeniMax.
All views and opinions expressed are those of the author.