Query help

[LanceRas]

Table that has the following as part of the columns:

Table X
FirstName | LastName | Score1 | Score2 | TotalScore | Grade
John | Doe | 101 | 102 | 203 | 10
Mary | Moe | 111 | 112 | 223 | 10
Herb | Garden | 121 | 122 | 243 | 9
Terry | Terrance | 131 | 132 | 263 | 10
John | Foe | 91 | 92 | 183 | 10


I'm trying to do a descending sort based on either score 1 or score 2 or total score within a certain grade, which isn't difficult. But what I'm trying to do is have a place column added, so that results show 1st, 2nd, 3rd, etc.

So looking for the top total scores for 10th graders, I could do a:

select FirstName, LastName, TotalScore where grade=10 order by TotalScore DESC

Would give me:
Terry Terrance 263
Mary Moe 223
John Doe 203
John Foe 183

I would like have a place number too, which would need to be calculated:

1 Terry Terrance 263
2 Mary Moe 223
3 John Doe 203
4 John Foe 183

Any suggestions?
Thanks!
Lance

[AlexP]

Hello,

To retrieve the record number, you can use calculated fields and return RecNo records in the onCalcFields event. Or use the following query:

Code: Select all

select 
(select count(*) from x as t2 where t2.TotalScore >= t1.TotalScore and grade=10) as RecNo,
FirstName, LastName, TotalScore 
from x as t1 
where grade=10 
order by TotalScore DESC

[LanceRas]

Alex,

that worked great!

Now... Taking it one step more complex, that I'm banging head.

Same schema, but adding one more row of sample data, which has a tie for 2nd place.

Table X
FirstName | LastName | Score1 | Score2 | TotalScore | Grade
John | Doe | 101 | 102 | 203 | 10
Mary | Moe | 111 | 112 | 223 | 10
Herb | Garden | 121 | 122 | 243 | 9
Terry | Terrance | 131 | 132 | 263 | 10
John | Foe | 91 | 92 | 183 | 10
Taylor | Tie | 111 | 112 | 223 | 10


Results I would like would be:

1 Terry Terrance 263
2 Mary Moe 223
Taylor Tie 223
4 John Doe 203
5 John Foe 183

or

1 Terry Terrance 263
2 Mary Moe 223
2 Taylor Tie 223
4 John Doe 203
5 John Foe 183

So that 3rd place is skipped because of the tie and that the place is represented correctly.

[AlexP]

To make the previous query work correctly, you need a field with unique data, basing on which a subquery will be generated.