How can I read the first n rows of table a based upon the number of rows in table b?

Question

I have a table "L20" that contains 1 to 20 values "HDIF" in it, sorted in ascending order. I need to extract the first 1 to 10 of those values into table "T10" depending upon the number of values in table "L20". I'm using Windows 10, Libreoffice 6.4.4, with Firebird 3 database. I've tried the CASE statement and the DECODE statement on the COUNT of rows in "L20", but neither seems to work.

If I put in a numeral for the SELECT on table "L20" then it works correctly. Anyone have an idea of how to solve? The purpose of this query is to calculate a golf handicap which uses [up to] the best (lowest) 10 scores of [up to] the last (most recent) 20 games played. Here is the coding:

/* Qry_Index_Calc - calculates handicap index from top 10 differentials of last 20 games  */
/* Source is "VW_Plyr_Diff" which has handicap differentials already calculated. */

SELECT (AVG ("T10"."HDIF") * .96) "Index", (Count ("T10"."HDIF")) FROM

/* Get only the games needed if less than 20 games have been played. */

    (
    SELECT FIRST 

        DECODE ((SELECT COUNT (*) FROM "L20"), 
              1,  1
            , 2,  1
            , 3,  1
            , 4,  1
            , 5,  1
            , 6,  1     
            , 7,  2
            , 8,  2
            , 9,  3
            , 10, 3
            , 11, 4
            , 12, 4
            , 13, 5
            , 14, 5
            , 15, 6
            , 16, 6
            , 17, 7
            , 18, 8
            , 19, 9
            , 10)

    "L20"."HDIF"

    FROM

/*  Get up to 20 of the most recent (last) games played.  */

            ( SELECT FIRST 20 "PlayerID" "PID", "GID" "GID", 
            RANK ( ) OVER ( PARTITION BY "PlayerID" ORDER BY "Diff" ) "Rnk", 
            "Diff" "HDIF", "Date" "Gdate"
            FROM "Vw_Plyr_Diff"
            WHERE "PlayerID" = 1) 

            "L20"   

    ) "T10"

Answer 1

You need to put parentheses around the expression in FIRST. As specified in the Firebird 3.0 Language Reference for FIRST, SKIP:

SELECT
  [FIRST <m>] [SKIP <n>]
  FROM ...
  ...

<m>, <n>  ::=
    <integer-literal>
  | <query-parameter>
  | (<integer-expression>)

So, use

select first (decode(...)) ....

When using subqueries directly in first, you need to use double parentheses (once for the expression, and once for the fact that sub-queries in expressions are enclosed in parentheses.

The SQL standard OFFSET/FETCH clauses introduced in Firebird 3 do not support expressions.

Beware, your current code doesn't specify an ORDER BY, this means it is undefined exactly which rows are returned, it will depend on location of data inside the database, the access plan, etc. I would recommend that you add an appropriate ORDER BY clause to ensure the returned rows are as expected.

It looks like you're trying to SELECT from the derived table L20 defined in the FROM clause, and not from an actual table L20. If you want to be able to do that, then L20 most be specified as a common table expression.