Devart Forums

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

Hello,

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

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.

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