Here is another more general way that will work in situations where RUNSUM may not - such as having duplicates in the column that you are counting. Also, if you change DISTINCT Score to SUM(Score) you will get a cumulative total of the score.

CREATE TABLE "StudentResults"
(
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"FullName" VARCHAR(24) COLLATE "ANSI_CI" NOT NULL,
"CourseID" INTEGER DEFAULT 0 NOT NULL,
"Score" INTEGER,
CONSTRAINT "srPrimaryIND" PRIMARY KEY ("ID"),
CONSTRAINT "srForeignKeyName" FOREIGN KEY ("FullName", "CourseID") REFERENCES "Students" ("FullName")
  ON UPDATE RESTRICT ON DELETE RESTRICT
)

CREATE VIEW "srResultsRanking" AS
SELECT ID, FullName, CourseID, Score,
 (SELECT Count(DISTINCT Score)
    FROM StudentResults AS SR1
    WHERE
      SR2.Score <= SR1.Score) AS Place
 FROM StudentResults AS SR2
ORDER BY Place


StudentResults
==============
"ID","FullName","CourseID","Score"
3,"Desdemona",1,50
4,"Desdemona",2,50
5,"Desdemona",3,48
7,"Rosalind",2,25
8,"Rosalind",3,25
9,"Rosalind",4,20
10,"Titania",2,15
11,"Titania",1,30
12,"Titania",2,40
20,"Titania",5,28

srResultsRanking
================
"ID","FullName","CourseID","Score","Place"
3,"Desdemona",1,50,1
4,"Desdemona",2,50,1
5,"Desdemona",3,48,2
12,"Titania",2,40,3
11,"Titania",1,30,4
20,"Titania",5,28,5
7,"Rosalind",2,25,6
8,"Rosalind",3,25,6
9,"Rosalind",4,20,7
10,"Titania",2,15,8

Richard

Tim Young [Elevate Software] wrote:

<<I think the confusion may be in that you're trying to use RUNSUM() in a non-grouped, non-single-row-result set context.  Aggregate functions only work in the context of grouped (GROUP BY) or single-row result sets.  If you use them in another context, you will get an error about a missing GROUP BY due to the other non-aggregate expressions.>>

Tim

The SQL standard says you can use the HAVING clause without a GROUP BY clause. If the GROUP BY clause is missing then the table is classed as one group.

I mentioned this before a long, long time ago you were going to check on it.

Richard