Welcome to WebmasterWorld Guest from 54.159.214.27

Forum Moderators: coopster & jatar k

select rows over and under specific row

   
4:14 am on Aug 1, 2012 (gmt 0)

5+ Year Member



hi guys!

I was wondering something. I have a list of teams with points.
I want to get a specific team and get 2 team over and 2 team under the selected team. These teams are ordered by points.

select * from standing where team=15 order by points desc

so lets say team 15 is ranked 7 then i would like to show teams ranked from 5 to 9.

i dont know if this is all clear but i hope you understand what i mean!
9:03 pm on Jan 20, 2013 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



This one slipped through the cracks, did you ever get this query nailed down, skoff?
9:08 pm on Jan 20, 2013 (gmt 0)

5+ Year Member



No it wasnt it could be nice if you have any idea on what i was asking!

Thanks
10:00 pm on Jan 25, 2013 (gmt 0)

10+ Year Member



For MySQL Try:

SELECT (SELECT a.team FROM standing a WHERE a.points < c.points ORDER BY a.Team = 15, a.points DESC LIMIT 1),
(SELECT b.Team FROM standing b WHERE b.points > c.points ORDER BY a.Team = 15, b.points LIMIT 1)
FROM standing c WHERE Team = 15

Basically the above does three queries at once - one to get Team 15's points, one to get the teams (that aren't #15) with less points, and one to get the teams (that aren't #15) with more points.

There are other ways to do it in SQL, but this way seems the easiest to understand.

-- Roger
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month