What is the simplest way to do "SELECT TOP 5" for multiple parameters?

Question

I'm trying to build a new database in LibreOffice Base / HSQLDB which supposedly supports standard SQL, but probably not quite as widely as SQL-server or Oracle.

I've got a table of scores, where the participant is a member of one of a list of clubs. I need to generate "Team Scores" where the teams are dynamic, namely they consist of the top 5 scores from each club.

The closest I have been able to achieve so far is:

SELECT "Score", "ShootRecords"."ClubID" FROM  "ShootRecords" WHERE "ShootRecords"."ClubID" = 0

UNION 

SELECT "Score", "ShootRecords"."ClubID" FROM  "ShootRecords" WHERE "ShootRecords"."ClubID" = 1

ORDER BY "Score" DESC

If I add a TOP 5 qualifier to the start of each sub-query, it reports as invalid SQL. If I move the ORDER BY clause into the sub-query, again it reports as invalid SQL, but each sub-query needs to be sorted that way, and restricted to TOP 5 or the results are wrong.

I've also looked at doing sub-queries like so:

SELECT "ClubID"

FROM   "Clubs"

WHERE  "Clubs"."ClubID" IN

   (SELECT "ClubID"

    FROM "Clubs", "ShootRecords"

    WHERE "Clubs"."ClubID" = "ShootRecords"."ClubID"
    )

but again the ordering and sub-setting is done in the wrong order, or isn't valid.

What I am hoping to see would look something like:

Score     ClubID
------------------
300        0
299        0
280        0
200        0
190        0
310        1
290        1
277        1

etc.

Should be pretty simple to do. Please add some sample data and expected result. — The Impaler, Jan 15 '19 at 13:29
Have you tried adding an `ORDER BY "Score" DESC LIMIT 5` to each unioned query ? ([ref](http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#dac_slicing)). Also, I think a `UNION ALL` would be better in this case. — LukStorms, Jan 15 '19 at 13:30
Also, `TOP 5` is not standard SQL. Append `LIMIT 5` (that is also not standard, but well supported). — The Impaler, Jan 15 '19 at 13:30
`LIMIT 5` isn't standard SQL either; `FETCH FIRST 5 ROWS` is. But, yes, HSQLDB wants `LIMIT 5`. — Thorsten Kettner, Jan 15 '19 at 13:32
What to do in case of ties? Pick a member from the tied ones arbitrarily I guess? — Thorsten Kettner, Jan 15 '19 at 13:33
According [this page](http://hsqldb.org/doc/guide/builtinfunctions-chapt.html) hsqldb supports also ROW_NUMBER? — LukStorms, Jan 15 '19 at 13:41
@The Impaler: `TOP 5` works in a stand-alone query, appending `LIMIT 5` doesn't work here - not valid. @Thorsten - one thing at a time! — MikeB, Jan 15 '19 at 13:41
Both `LIMIT 5` and `ORDER BY` give me an `Unexpected token` error if I add them to the second sub-query, but work fine, (albeit limited) on the first sub-query. — MikeB, Jan 15 '19 at 13:49
@ThorstenKettner actually, ties at this level don't matter in the slightest - the total team score (which I hadn't mentioned yet) is what I was after, so it doesn't matter who achieves it. — MikeB, Jan 15 '19 at 13:59
I don't understand. If a team has these scores: 500, 400, 300, 200, 200, 200, 100, you want to do something about the tying 200, supposedly retrieve it twice, so you get five scores 500, 400, 300, 200, 200. Correct? — Thorsten Kettner, Jan 15 '19 at 14:01
@ThorstenKettner Excactly right - I want to select the five best scores, regardless of who achieved them. — MikeB, Jan 15 '19 at 14:26

score 2 · Accepted Answer · answered Jan 15 '19 at 13:56

2

To make your own query work, you'd want an ORDER BY (and LIMIT) clause per partial query. In order to do so, use parentheses:

(
  SELECT Score, ClubID 
  FROM ShootRecords 
  WHERE ShootRecords.ClubID = 0
  ORDER BY Score DESC
  LIMIT 5
)
UNION ALL
(
  SELECT Score, ClubID 
  FROM ShootRecords 
  WHERE ShootRecords.ClubID = 1
  ORDER BY Score DESC
  LIMIT 5
)
ORDER BY ClubID, Score DESC;

It's UNION ALL by the way. UNION [DISTINCT] is a special form of union that removes duplicates.

answered Jan 15 '19 at 13:56

Thorsten Kettner

89,309
7
49
73

This was it - the first time I had seen an example of `UNION` with brackets placed around the sub-queries. The rest was pretty much fine, just not being parsed as two distinct queries. – MikeB Jan 15 '19 at 14:34

score 0 · Answer 2 · answered Jan 15 '19 at 13:39

TOP n queries per group are usually solved with analytic functions, but HSQLDB doesn't support them.

Anyway, to be among the top five means there are less than five better.

select *
from  shootrecords sr
where 5 >
(
  select count(*)
  from shootrecords better
  where better.clubid = sr
  and 
  (  better.score > sr.score
      or 
    (better.score = sr.score and better.memberid < sr.memberid) -- for the case of ties
  )
)
order by clubid, score, memberid;

In case of a tie (multiple members with the same score) I must pick members arbitrarily. I pick those with the lowest IDs first.

What is the simplest way to do "SELECT TOP 5" for multiple parameters?

2 Answers2