I'm having a brain clog.

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

On 13/10/2015 12:11 p.m., Lance Rasmussen wrote:
>
>
> select FirstName, LastName, TotalScore where grade=10 order by TotalScore DESC
>
Try:-

   SELECT FirstName, LastName, TotalScore, RUNSUM(1)
   WHERE Grade=10
   ORDER BY TotalScore DESC

Cheers

Jeff

Jeff,

Ahem....

   SELECT FirstName, LastName, TotalScore, RUNSUM(1) AS Place
   WHERE Grade=10
   ORDER BY TotalScore DESC

There, fixed it.

Tim Young
Elevate Software
www.elevatesoft.com

HI Lance, Jeff and Tim

If it is important to give students with the same SCORE the same PLACE ranking then you could try this.
Otherwise, someone may get upset and disappointed at receiving a lower PLACE than someone with the same SCORE.

SELECT FirstName, LastName, TotalScore,
     (SELECT COUNT(DISTINCT TotalScore)
        FROM X AS X2
        WHERE X2.TotalScore >= X1.TotalScore) AS Place
  FROM X AS X1
  WHERE Grade=10
  GROUP BY ID
  ORDER BY TotalScore DESC

Richard Harding

On 22/10/2015 3:00 p.m., Richard Harding wrote:
> HI Lance, Jeff and Tim
>
> If it is important to give students with the same SCORE the same PLACE ranking then you could try this.
> Otherwise, someone may get upset and disappointed at receiving a lower PLACE than someone with the same SCORE.
>
> SELECT FirstName, LastName, TotalScore,
>        (SELECT COUNT(DISTINCT TotalScore)
>           FROM X AS X2
>           WHERE X2.TotalScore >= X1.TotalScore) AS Place
>     FROM X AS X1
>     WHERE Grade=10
>     GROUP BY ID
>     ORDER BY TotalScore DESC
>
> Richard Harding
>

Cool!

As this seems to be developing into a little competition, can I ramp it
up a notch or two?

I need to rank sports results and (with my sport at least) it is
customary to use the absolute place unless there is a tie.  So, in the
following example, note the absence of the places 3 and 4:

1  Malcolm 310.00
2  Bill  305.00
2  Tom  305.00
2  Roco  305.00
5  James  300.00

So using COUNT(DISTINCT..) does not do the job.  Instead I need to use
RUNSUM() but somehow override it for ties.  

When that little matter is solved, the next challenge I have to manage
is guest competitors!  Guests are to be given their absolute place (but
also taking into account guests tied with guests!) but this must not
affect the non-guest places.  Yikes!  Take this example:

1  Malcolm 310.00
2  Bill  305.00
2  Tom  305.00
4  Roco (guest)  305.00
4  James  300.00

At present I pop the required data into a temporary table with an index
on the score then use table navigation to traverse it and insert the
places based on the necessary running counts as well as managing the
two types of potential ties.

But a nice piece of SQL would be welcome ...   
Not holding breath!

Hi Malcolm

How about something like this?

CREATE TABLE "Contacts"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"FName" VARCHAR(24) COLLATE "ANSI",
"Score" DECIMAL(19,2),
"NonGuest" INTEGER    -- 0=Guest & 1=NonGuest
)


Create a view, removing the DISTINCT from the COUNT.

CREATE VIEW "rkOrderByBalance" AS
SELECT ID,  FName, Score, NonGuest,
     (SELECT COUNT( Score* 100 + NonGuest)
        FROM Contacts C2
        WHERE C2.Score* 100 + C2.NonGuest >= C1.Score* 100 + C1.NonGuest) AS Place
 FROM Contacts C1
 ORDER BY ScoreDESC, NonGuest DESC

This would give you, which is getting close.

1  Malcolm 310.00
3  Bill  305.00
3  Tom  305.00
4  Roco (guest)  305.00
5  James  300.00

Create another view that counts the number of people that are in the same position.

CREATE VIEW "rkRankCount" AS
SELECT Score, NonGuest, COUNT(*) AS PlaceCount
 FROM Contacts C1
 GROUP BY Score, NonGuest

Join the views together

SELECT FName, Score, NonGuest, Place, PlaceCount, Place-PlaceCount+1 AS NewPlace
 FROM
     rkOrderByBalance AS R1
        INNER JOIN rkRankCount AS R2 ON R1.Score = R2.Score AND R1.NonGuest = R2.NonGuest

Which I think gives the answer.  Maybe??

Richard

Richard Harding wrote:

> Hi Malcolm
>
> How about something like this?
>

Wow, I could nearly have held my breath!
When I get a few moment I will give that a whirl to see if it hits the
spot, or gets close.  
.......

Of course, there is then the matter of overall ranking of prelims,
semi-finals and finals.  But if I can switch to SQL for finals the rest
will be a simple(!) extension - ranking each qualifying stage starting
with the appropriate, ID seed.

Richard,

<< If it is important to give students with the same SCORE the same PLACE ranking then you could try this.
Otherwise, someone may get upset and disappointed at receiving a lower PLACE than someone with the same SCORE. >>

Nice.

Tim Young
Elevate Software
www.elevatesoft.com

Richard Harding wrote:

>
> Which I think gives the answer.  Maybe??
>

Blimey .. it almost does!  
With a lot less code than my table navigation.
And much safer for me to use Tim's SQL implementation than my
navigational spaghetti.

Only thing I have to do now is fix the error in my sample and tweak the
solution accordingly.
Roco (guest) should actually be placed 2 as that was his absolute rank.


I will go over it again in the morning when the fog has cleared.  
Many thanks for your insight.