SQL (newb help please?) | Tech Off

mnuber

Hey guys sorry to bother you, but I am a complete newbie at ASP and SQL, and I hope you guys could help me?

What I'm doing:

I have people and their scores in a table, what I would like to do is find the 10 closest scores in the table, does this make sense?
(SQL=... Wink

Any help?

If it helps...
-table contains the colums: personID, person, pScore1 - pScore10, totalScore.

Thanks! I can answer any question you have to help you help me.

-mnuber

daxmax

Select Top 10 * from
(Select Top 10 personID,PScore1 as Score ,"Score 1" as "ScoreType" from table order by pScore1
Union
Select Top 10 personID,PScore2 as Score , "Score 2" as "ScoreType" from table order by pScore2
Union
Select Top 10 personID,PScore3, "Score 3" as "ScoreType" from table order by pScore3
etc... /* FOR ALL TEN SCORES*/
) as TablewithTop100Scores order by Score

However if you just want the top ten for the totals then

Select Top 10 * from table order by TotalScore

That's the SQL. The ASP could would open the connection, run the query , and then return the data in a specifc recording holding format, like a recordset.

Matthew van Eerde
AKA Maurits

What do you mean by "closest scores"?

SecretSoftware
Code to live, but Live to code.

Matthew van Eerde wrote:
What do you mean by "closest scores"?

The ones with the smallest degree of difference?

Matthew van Eerde
AKA Maurits

SecretSoftware wrote:

Matthew van Eerde wrote:What do you mean by "closest scores"?

The ones with the smallest degree of difference?

Heh.

Well, are we looking for:

a) the people who scored closest to "perfect"...
b) the people who scored the most consistently*
c) the ten people who scored closest to each other, based on the total alone
d) the ten people who scored closest to each other based on all ten individual scores

* that is, the difference between their highest score and their lowest score is smaller than anybody else's

SecretSoftware
Code to live, but Live to code.

Matthew van Eerde wrote:

SecretSoftware wrote:
Matthew van Eerde wrote:What do you mean by "closest scores"?

The ones with the smallest degree of difference?

Heh.

Well, are we looking for:

a) the people who scored closest to "perfect"...
b) the people who scored the most consistently*
c) the ten people who scored closest to each other, based on the total alone
d) the ten people who scored closest to each other based on all ten individual scores

* that is, the difference between their highest score and their lowest score is smaller than anybody else's