How to make this selection? - SQL Server Forums

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)

How to make this selection?

Author

Topic

eelco
Starting Member

2 Posts

Posted - 2003-07-10 : 05:10:24

Hello,

I'm not a developer, just a newbee.
I wonder how to make a query for the following selection:

Table Players (it's different but like this one)

Rank Name Points
----------------
1 AA 340
2 BB 256
3 CC 346
4 DD 289
5 EE 280

Now I want to select all players, and in the same record the player (with stats) where he must play against next round. There is an simple order in it: only the first 4 players will play next round, first against last one, etc. :

Result:

Rank Name Points Opponent Points
--------------------------------
1 AA 340 DD 289
2 BB 256 CC 346
3 CC 346 BB 256
4 DD 289 AA 340
5 EE 280

What kind of query do I need?

Thanks a lot, Eelco

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-10 : 05:46:10

/*
drop table players
create table players (rank int identity, [name] varchar (2), points int null)

insert into players values ('AA', 340)
insert into players values ( 'BB', 256)
insert into players values ( 'CC', 346 )
insert into players values ( 'DD', 289 )
insert into players values ( 'EE', 280 )

select * from players
*/
/*

create table #moo (rev int identity, plays varchar(2))

insert into #moo (plays)
select [name] from players
where points > (select min (points) from players)
order by points desc

SELECT * from #moo

drop table #moo

*/
--At this point I got confused, because I thought you meant first (highest scoring) against last (lowest scoring).

--But looking at your table it seems to be ordered by rank regardless of score..

--So instead we need

go
create table #moo (rev int identity, plays varchar(2), points int null)

insert into #moo (plays, points)
select [name], points from players
where rank < (select max (rank) from players)
order by rank desc

SELECT * from #moo

--Then we can do

SELECT players.rank, players.[name], players.points, #moo.plays as [opponent], #moo.points as [points]
FROM
players
left outer join #moo on
#moo.rev = players.rank

-------
Moo. :)

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-10 : 05:47:28

P.S. That probably falls over if there is an even number of players in total.

-------
Moo. :)

eelco
Starting Member

2 Posts

Posted - 2003-07-10 : 17:25:40

OK, that is an solution, thanks.

So I can't make it in one SQL-statement?

Something like

select a.rank, a.name, a.points, b.name, b.points
from players as a left join players as b
on ? = ? <-- can I give an formula in here?

I hope it's possible to do this without the need of an extra view or table.

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-11 : 03:20:27

quote:

OK, that is an solution, thanks.

So I can't make it in one SQL-statement?

Hmm. Good question. You probably can, but I cannot. Not right now at least. :D

-------
Moo. :)

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-07-11 : 11:11:14

Declare @t table(Rank int,Name varchar(10), Points int)

insert into @t select 1 ,'AA', 340
insert into @t select 2 ,'BB', 256
insert into @t select 3 ,'CC', 346
insert into @t select 4 ,'DD', 289
insert into @t select 5 ,'EE', 280

select Rank ,Name,Points ,
(select Points from @t b where a.Rank + b.Rank = (select max(Rank ) from @t c)) as Points,
(select Name from @t b where a.Rank + b.Rank = (select max(Rank ) from @t c)) as Opponent
from @t a

Hope this would help u

V.Ganesh
Net Asset Management
vganesh76@rediffmail.com

Enjoy working

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-07-11 : 11:50:30

To just drop the lowest ranked player use...

select p1.rank, p1.player, p1.points, p2.player, p2.points
from players p1 left join
(select player, rank-1 as pairing ,points from players) p2
on p1.rank = p2.pairing

To mimic the ordering scheme you have where the top ranked player matches to the lowest ranked remaining player use...

select p1.rank, p1.player, p1.points, p2.player, p2.points
from players p1 left join
(select player, abs(rank-(select max(rank) from players)) as pairing ,points from players) p2
on p1.rank = p2.pairing

hth,
Justin

"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources