Championship table, adding top 9 results

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

General SQL Server Forums

New to SQL Server Programming

Championship table, adding top 9 results

Author

Topic

chricholson
Starting Member

6 Posts

Posted - 2010-02-19 : 14:04:35

I have a scenario whereby users are in a championship in which 12 races are held over 12 weeks. Each week they recieve points from 40 to 0 depending on where they finish (1st - 40, 2nd - 38....).

An example row may look like this:
Joe Bloggs 12 14 16 14 14 18 - 12 10 14 16 14
where the first column is their name and the next 12 are the points recieved for each week. The (-) refers to a week where they were not present and therefore recieved no points. Unfortunately this cannot be represented as 0 as this implies that they attended but did not score high enough to recieve any points.

The challenge:
I need to add a new column on the end which will return the total score based on each week, so 12+14+16+14....
Even more complicated, I need to return only the top 9 highest results, as the lowest 3 weeks scores are dropped to be kind to those who had bad weeks.

I initially had a table containing 13 columns, one for the name and a column representing each week. However I cannot find a suitable SQL query which will add the highest 9 across columns (as I believe TOP # will only work for rows).

I was later told about using an INNER JOIN function but I have not used these before so I am not entirely sure what I should be implementing.

The columns do not need to be dynamic as there is always 12 so this does not need to be dynamic. I can also if needed place the week results or any other information in a second or third table if necessary.

Thank you for taking the time to read this, I look forward to any suggestions.

Chris

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 14:11:51

better to keep the score as NULL for weeks they were absent. then do like


SELECT Name,SUM(Score) AS Total
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Score DESC) AS Seq,Name,Score 
FROM
(
SELECT Name ,COALESCE(col1,0) AS Score
FROM Table
UNION ALL
SELECT Name ,COALESCE(col2,0)
FROM Table
UNION ALL
SELECT Name ,COALESCE(col3,0)
FROM Table
..

SELECT Name ,COALESCE(col12,0) 
FROM Table
)t
)r
WHERE Seq < =9
GROUP BY Name

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

Sachin.Nand

2937 Posts

Posted - 2010-02-19 : 14:22:19

If you want to use TOP then you can do something like this


select top 9 scores,name from
(
select name,col1 from table
union all
select name,col2 from table
.
.
.
.
.
.
select name,col12 from table
)t(name,scores)order by scores desc

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 14:24:38

quote:
Originally posted by Idera

If you want to use TOP then you can do something like this

select top 9 scores,name from
(
select name,col1 from table
union all
select name,col2 from table
.
.
.
.
.
.
select name,col12 from table
)t(name,scores)order by scores desc

PBUH

that will always return top 9 rows alone but i think what op wants is top 9 scores per user and get their total

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

Sachin.Nand

2937 Posts

Posted - 2010-02-19 : 14:28:15

But I have put an order by clause.So I think he will get top 9 scores if I am not mistaken



select top 2 x from
(
select 1 
union all
select 2
union all
select 3
union all
select 4

)t(x)group by x order by x desc

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-02-19 : 14:36:52

To get the sum


select sum(scores),name from
(
select top 9 scores,name from
(
select name,col1 from table
union all
select name,col2 from table
.
.
.
.
.
.
select name,col12 from table
)t(name,scores)order by scores desc
)T1

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 23:56:55

quote:
Originally posted by Idera

But I have put an order by clause.So I think he will get top 9 scores if I am not mistaken

select top 2 x from
(
select 1
union all
select 2
union all
select 3
union all
select 4

)t(x)group by x order by x desc

PBUH

Sorry but you missed my point
He will get top 9 rows alone not for each user.
I think his reqmnt is to find total for each user taking only his top 9 scores out of total 12

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

Sachin.Nand

2937 Posts

Posted - 2010-02-20 : 02:10:08

quote:
Originally posted by visakh16

quote:
Originally posted by Idera

But I have put an order by clause.So I think he will get top 9 scores if I am not mistaken

select top 2 x from
(
select 1
union all
select 2
union all
select 3
union all
select 4

)t(x)group by x order by x desc

PBUH

Sorry but you missed my point
He will get top 9 rows alone not for each user.
I think his reqmnt is to find total for each user taking only his top 9 scores out of total 12

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

Oh OK understood

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 02:11:39

quote:
Originally posted by Idera

quote:
Originally posted by visakh16

quote:
Originally posted by Idera

But I have put an order by clause.So I think he will get top 9 scores if I am not mistaken

select top 2 x from
(
select 1
union all
select 2
union all
select 3
union all
select 4

)t(x)group by x order by x desc

PBUH

Sorry but you missed my point
He will get top 9 rows alone not for each user.
I think his reqmnt is to find total for each user taking only his top 9 scores out of total 12

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

Oh OK understood

PBUH

Cool

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

chricholson
Starting Member

6 Posts

Posted - 2010-02-20 : 08:31:03

Visakh is right in that I need to add up the top 9 scores for each user so it is only looking at one row at a time, hence why I didn't believe the TOP function would work (don't have to use it that's just the only thing I tried).

I have not had a chance to try your answers yet but many thanks for your swift replies, I will attempt both and let you know how I get on with them.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 08:58:44

quote:
Originally posted by chricholson

Visakh is right in that I need to add up the top 9 scores for each user so it is only looking at one row at a time, hence why I didn't believe the TOP function would work (don't have to use it that's just the only thing I tried).

I have not had a chance to try your answers yet but many thanks for your swift replies, I will attempt both and let you know how I get on with them.

Sure try them out and let us know how you got on

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

chricholson
Starting Member

6 Posts

Posted - 2010-02-21 : 15:35:49

Perfect! That works EXACTLY how I need it too, thank you very much for your help Visakh; a very clean, knowledgable answer so many thanks once again.

Many thanks to you as well Idera for your contribution, if not quite clear of my original goal.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 08:11:57

quote:
Originally posted by chricholson

Perfect! That works EXACTLY how I need it too, thank you very much for your help Visakh; a very clean, knowledgable answer so many thanks once again.

Many thanks to you as well Idera for your contribution, if not quite clear of my original goal.

Great

You're welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources