multiple queries in a query - 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 2005 Forums

Transact-SQL (2005)

multiple queries in a query

Author

Topic

kingjeremy
Starting Member

10 Posts

Posted - 2009-05-05 : 09:30:56

[tournaments table]
tId, tName
1, theTour
...

[tournamentRounds table]
tRoundId, tId, tRoundName, tRoundDate
1, 1, , firstRound, feb-1-2009
2, 1, , secondRound, feb-2-2009
...

[tournamentPlayers table]
tPlayerId, tId, playerId
1, 1, 1
2, 1, 2
....

[playerScores table]
pScoreId, playerId, score, scoreDate
1, 1, 9, jan-20-2009
2, 1, 10, jan-29-2009
3, 1, 11, feb-1-2009
4, 1, 12, feb-10-2009
5, 2, 20, jan-29-2009
6, 2, 21, feb-12-2009

....

Each tournament can have one or more tournamentRounds
Every tournamentPlayer plays in all rounds
What I'm trying to query is to list a table like below

tId, tName, tPlayerId, score
1, theTour, 1, 10
1, theTour, 2, 20
....

the trick is the score should be the last score before the firstRound date
How can I write this query.
Thanks for any help.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-05-05 : 09:36:43

what is the data type of the fields tRoundDate and scoreDate?

kingjeremy
Starting Member

10 Posts

Posted - 2009-05-05 : 09:45:40

it is dateTime I just write it this way to be more readable

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-05 : 09:59:26

[code]
declare @tournaments table
(
tId int,
tName varchar(10)
)
insert into @tournaments
select 1, 'theTour'

declare @tournamentRounds table
(
tRoundId int,
tId int,
tRoundName varchar(20),
tRoundDate datetime
)
insert into @tournamentRounds
select 1, 1, 'firstRound', '2009-02-01' union all
select 2, 1, 'secondRound', '2009-02-02'

declare @tournamentPlayers table
(
tPlayerId int,
tId int,
playerId int
)
insert into @tournamentPlayers
select 1, 1, 1 union all
select 2, 1, 2

declare @playerScores table
(
pScoreId int,
playerId int,
score int,
scoreDate datetime
)
insert into @playerScores
select 1, 1, 9, '2009-01-20' union all
select 2, 1, 10, '2009-01-29' union all
select 3, 1, 11, '2009-02-01' union all
select 4, 1, 12, '2009-02-10' union all
select 5, 2, 20, '2009-01-29' union all
select 6, 2, 21, '2009-02-12'

-- Query
select tId, t.tName, t.tPlayerId, t.score
from
(
select t.tId, t.tName, tr.tRoundId, tr.tRoundName, tr.tRoundDate, tp.tPlayerId, tp.playerId, ps.pScoreId, ps.score, ps.scoreDate,
row_no = row_number() over (partition by tp.tPlayerId order by tr.tRoundDate, ps.scoreDate desc)
from @tournaments t
inner join @tournamentRounds tr on t.tId = tr.tId
inner join @tournamentPlayers tp on t.tId = tp.tId
inner join @playerScores ps on tp.playerId = ps.playerId
where ps.scoreDate < tr.tRoundDate
) t
where t.row_no = 1
[/code]

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

kingjeremy
Starting Member

10 Posts

Posted - 2009-05-05 : 10:31:38

Thank you very much khtan this really does the trick for me, and I must confess I didn't knew that there was a rank-related function in mssql.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources