select rows over and under specific row

[skoff]

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!

[coopster]

This one slipped through the cracks, did you ever get this query nailed down, skoff?

[skoff]

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

Thanks

[jadebox]

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