January 21, 2008 at 5:41 am
Hi i am using the following code to create a league table but i have no way of identifying which position the club are in the table so im just wondering if ayone knows of anyway to put a rod id on the rows of the results set, any help would be appreciated
Thanks in advance
Tim
Select '?????????' AS 'Row Number',
c.name as 'Team',
3*((
select count(*) from matchstats
where hometeamftscore > awayteamftscore
and matchid IN (select matchid from matchstats, match
where (match.squada = s.id)
and matchstats.matchid = match.id
and match.competitionid = 0
And Date Between (Select StartDate From Seasons Where ID = 148)
And (Select EndDate From Seasons Where ID = 148))
)+
(
select count(*) from matchstats
where hometeamftscore < awayteamftscore
and matchid IN (select matchid from matchstats, match
where (match.squadb = s.id)
and matchstats.matchid = match.id
and match.competitionid = 0
And Date Between (Select StartDate From Seasons Where ID = 148)
And (Select EndDate From Seasons Where ID = 148))
))+(
select count(*) from matchstats
where hometeamftscore = awayteamftscore
and matchid IN (select matchid from matchstats, match
where (match.squada = s.id or match.squadb = s.id)
and matchstats.matchid = match.id
and match.competitionid = 0
And Date Between (Select StartDate From Seasons Where ID = 148)
And (Select EndDate From Seasons Where ID = 148))
) as 'Pts'
from club c,
squad s,
match m,
matchstats ms
where s.clubid = c.id
and m.competitionid = 0
and (m.squada = s.id or m.squadb = s.id)
and m.id = ms.matchid
And m.Date Between (Select StartDate From Seasons Where ID = 148)
And (Select EndDate From Seasons Where ID = 148)
group by s.id, c.name
Order By Pts Desc
January 21, 2008 at 5:56 am
Since you seem to be on SQL Server 2000 you cannot use row_number(). You could create a temporary table for the whole result, and populate it using:
select identity(int, 1,1) as RowNumber, other_columns into #foo from ....
This will create an extra column that will contain a row number.
Calculated row numbers are an option if your returned result set is small or poor performance is not an issue.
Regards,
Andras
January 21, 2008 at 6:39 am
Hi and thanks for ur reply i managed to get this working in Query Analyser but i was hoping to use the code in a
Function and i am getting an error about using temporary tables in Functions, do u know of any other way i could get these results
Thanks again
Tim
January 21, 2008 at 6:58 am
tadhg88 (1/21/2008)
Hi and thanks for ur reply i managed to get this working in Query Analyser but i was hoping to use the code in aFunction and i am getting an error about using temporary tables in Functions, do u know of any other way i could get these results
Thanks again
Tim
Well,
you cannot use a temp table there, but you could use a table variable :). However, table variables have their drawbacks (do read about them on http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx and http://support.microsoft.com/?kbid=305977) (much depends on how much data you have)
You could however use something like
declare @t table(a int identity(1,1), b int)
insert into @t (b) select 1 from sysobjects
The above is just an example, but basically it creates a table variable with an identity column. But, at this moment it may be worth considering not to use a user defined function, do some ugly select for simulating row numbers on 2000 (http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133) (this approach is slow too), think about 2005 :), think about doing this in code (like c#)
Regards,
Andras
January 21, 2008 at 7:23 am
ok i have got this working but it is very slow so i will check out the other options u suggested and see if i can find something faster
Thanks for ur replies they are appreciated
Tim
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply