Multiple column query taking best 8 scores.

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

Transact-SQL (2000)

Multiple column query taking best 8 scores.

Author

Topic

winston1983
Starting Member

1 Post

Posted - 2004-06-03 : 08:54:45

I have a database table which can NOT be altered.

It is structured with the following fields:

Name, 1, 2, 3, 4,5,6,7,8,9,10,11,12,13,14, Total

Each club member has 14 matches, they score points for each. Only your best 8 scores from the 14 matches count. I need to have a query which will retrieve only these values plus the name and total. So I can display them on an ASP page. Can anyone help?
Regards,

Lewis Harvey
lewis@lharvey.com

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-06-03 : 10:02:01

It is a *LOT* easier to do this when these values are represented as rows instead of columns, as they would have been if the schema was normalized. But I understand your situation, know there is nothing that can be done to change it, so let's find another solution. So you basically need to unpivot this data and select the top eight. Are the columns really named 1, 2, 3...etc? I've used score1, score2, etc. First we need to convert the columns to rows:


Query A

SELECT Name, 1 AS Match, Score1 AS Score FROM Matches WHERE yada
UNION ALL
SELECT Name, 2, Score2 FROM Matches WHERE yada
UNION ALL
SELECT Name, 3, Score3 FROM Matches...
...
SELECT Name, 14, Score14 FROM Matches...

This should give you the scores in rows. Now to pick the top 8, relatively easy:


Query B

SELECT TOP 8 * FROM
(
 --INSERT Query A here
) B
ORDER BY Score DESC

How does it look?

EDIT: Fixed couple of bugs

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources