Returning a row, and two rows above it and below

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Returning a row, and two rows above it and below

Author

Topic

cooleofooleo
Starting Member

2 Posts

Posted - 2007-06-15 : 08:18:10

Hi guys, sorry if this question has been asked before. Basically I have a league (like a soccer league), and instead of showing the whole league, I just want to return my own Team, as well as the 2 teams above it, and below it.
So I select * from league, and order it by points - then i want to return just 5 rows from the league, or which, my team is in the middle!

Sorry for the complex description, but dont quite know what sql code i should be looking for.

Hopefully/Ideally, if my team is at the top of the league, it will be returned along with the 4 teams directly below it, and the same with my team being on the bottom of the league, 4 teams directly above it will be returned.

Appreciate any help! Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-15 : 08:37:29

something like

with a (teamname, seq)
as
(
select teamname, seq = (select count(*) from league l2 where right(space(10) + convert(varchar(10),l2.points),10) + l2.teamname <= right(space(10) + convert(varchar(10),l.points),10) + l.teamname
from league l
) t
select top 5 l.*
from league l
join a
on l.teamname = a.teamname
join a a2
on a2.teamname = @myteamname
where a.seq > a2.seq - 2
order by a.seq

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources