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

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.

Solution

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.