Selecting a record and 2 before and after it

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 2000 Forums

SQL Server Development (2000)

Selecting a record and 2 before and after it

Author

Topic

lstephan
Starting Member

12 Posts

Posted - 2011-09-30 : 12:09:53

Hi,
I ve made a table league for football (ot works fine) but now i would like to select my favorite team and juste the 2 teams before and after my favorite one.

My SQL query :

SELECT masomme, equipe_id, name, logo_small, diff, win, equal, lost, joues, ROW_NUMBER() OVER (ORDER BY masomme DESC, diff DESC, win DESC, lost ASC)
AS RowNumber
FROM lstephan.classementgeneral

So i can choose my favorite team by (where equipe_id =4) but i would like to choose 2 before and 2 after...

Any idea?

Thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 12:14:18

[code]
DECLARE @FavTeamID int
SET @FavTeamID=4
SELECT *
FROM
(
SELECT masomme, equipe_id, name, logo_small, diff, win, equal, lost, joues, DENSE_RANK() OVER (ORDER BY (equipe_id-@FavTeamID) ASC)
AS rn
FROM lstephan.classementgeneral
)t
WHERE rn <=3
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lstephan
Starting Member

12 Posts

Posted - 2011-09-30 : 12:28:48

Hi
Thanks a lot visakh16 for your response

But i probably don't explain my problem as well...

My ranking give me all teams, their points and position (rank).

Like this

rank team points idteam

1 team xy 30 5
2 team abc 29 1
3 team qsd 25 8

8
9
10 myteam 10 4
11
12

So i want just to display myteam and 2 teams before and after

Is it more clear?

Thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 12:33:55

[code]
declare @teamname varchar(100)

set @teamname='myteam'
;With cte
as
(
select *
from table
where team = @teamname
union all
select t.*
from table t
join cte c
on abs(c.rank-t.rank) = 2
)

select * from cte
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lstephan
Starting Member

12 Posts

Posted - 2011-09-30 : 12:40:19

Thanks a lot but sorry i dont really understand

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 12:48:29

did you try it first?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lstephan
Starting Member

12 Posts

Posted - 2011-09-30 : 12:51:32

Yes i tried it.

My query for generating rank :

SELECT masomme, equipe_id, name, logo_small, diff, win, equal, lost, joues, ROW_NUMBER() OVER (ORDER BY masomme DESC, diff DESC, win DESC, lost ASC)
AS RowNumber
FROM lstephan.classementgeneral

And i m a poor sql developper lol

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 13:18:19

[code]
declare @teamname varchar(100)

set @teamname='myteam'

SELECT masomme, equipe_id, name, logo_small, diff, win, equal, lost, joues, ROW_NUMBER() OVER (ORDER BY masomme DESC, diff DESC, win DESC, lost ASC)
AS RowNumber INTO #temp
FROM lstephan.classementgeneral

;With CTE
AS
(
SELECT * FROM
#Temp
WHERE name= @name
UNION ALL
SELECT t.*
FROM #Temp t
INNER JOIN CTE c
ON ABS(c.RowNumber-t.RowNumber)<=2
AND c.c.RowNumber<>t.RowNumber
)

SELECT *
FROM CTE
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lstephan
Starting Member

12 Posts

Posted - 2011-09-30 : 14:30:26

Sorry i must be so stupid lol

But when i tried to execute it , i ve got error.

I really dont understand why using
some "variable" :

declare @teamname varchar(100)

set @teamname='myteam'

SELECT masomme, equipe_id, name, logo_small, diff, win, equal, lost, joues, ROW_NUMBER() OVER (ORDER BY masomme DESC, diff DESC, win DESC, lost ASC)
AS RowNumber INTO #temp
FROM lstephan.classementgeneral (here ok)

;With CTE
AS
(
SELECT * FROM
#Temp
WHERE name= @name
UNION ALL
SELECT t.*
FROM #Temp t
INNER JOIN CTE c
ON ABS(c.RowNumber-t.RowNumber)<=2
AND c.c.RowNumber<>t.RowNumber
)

SELECT *
FROM CTE

I dont understand (bold expressions)

But dont want you to spend too much time on it...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-01 : 14:03:46

its place where you check for 2 records before and after your rank. abs(difference) =2 will give same record as well as 2 before and 2 after. c.c.RowNumber<>t.RowNumber is to exclude same record from coming again

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lstephan
Starting Member

12 Posts

Posted - 2011-10-02 : 03:17:19

Thanks a lot i will try it.

Have a nice day

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-02 : 03:35:26

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lstephan
Starting Member

12 Posts

Posted - 2011-10-02 : 04:06:35

Hi,

I didnt try it yet but just some explication

What is CTE, C and T for?

Is it referring to tables?

Sorry but i m just trying to understand but i don t want to disturb you anymore.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-02 : 04:12:37

CTE is name i gave for common table expression. you can give any name instead

c and t are aliases ie short names

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lstephan
Starting Member

12 Posts

Posted - 2011-10-02 : 04:29:32

Ok fine thanks a lot i will work on it

lstephan
Starting Member

12 Posts

Posted - 2011-10-02 : 05:50:24

I ve tried this :

declare @teamname varchar(100)

set @teamname='stade brestois'

SELECT masomme, equipe_id, name, logo_small, diff, win, equal, lost, joues, ROW_NUMBER() OVER (ORDER BY masomme DESC, diff DESC, win DESC, lost ASC) 
AS RowNumber INTO #temp
FROM lstephan.classementgeneral 

;With CTE
AS
(
SELECT * FROM 
#Temp
WHERE name=@teamname

UNION ALL
SELECT t.*
FROM #Temp t
INNER JOIN CTE c
ON ABS(c.RowNumber-t.RowNumber)<=2
AND c.RowNumber<>t.RowNumber
)

SELECT *
FROM CTE

It selected the right rows but returns me 252 rows i dont know why

Thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-02 : 07:44:35

will your RowNumber repeat?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lstephan
Starting Member

12 Posts

Posted - 2011-10-02 : 10:46:35

Hi yes it did.

But i could just do select top 4 no?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-02 : 10:49:29

[code]
ROW_NUMBER() OVER (ORDER BY masomme DESC, diff DESC, win DESC, lost ASC) AS RowNumber
[/code]
How is it possible for the above RowNumber to have repeating value ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-02 : 13:39:55

thats what i also couldnt make out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

lstephan
Starting Member

12 Posts

Posted - 2011-10-03 : 02:08:39

Hi,

I will test it again today.

Thanks a lot.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources