On Fri, Dec 13, 2019 at 2:05 AM Mark Rotteveel mark@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

 

On 2019-12-13 08:14, Kjell Rilbe kjell.rilbe@...
[firebird-support] wrote:
> Den 2019-12-12 kl. 22:36, skrev Clyde Eisenbeis cte677@...
> [firebird-support]:
>> Marcin, Minor tweak => works!  I also added DESC to retrieve the
>> newest records using the field key name (number is an auto increment).
>> `````````````````
>> "SELECT FIRST " + stNumOfRecordsToRetrieve + " * FROM " + stTableName
>> + " ORDER BY " + fstKeyID + " DESC";
>> `````````````````
>> It's interesting that the newest records are found first (DESC), then
>> the number of records retrieved are from those DESC record results..
>
>
> Yes, the "subset clause" is applied last of all, so specified ordering
> will be respected.
>
> Unless I'm mistaken, FIRST N is Firebird specific. The SQL standard
> syntax for this is:
>
> SELECT *
> FROM T
> ORDER BY YOUR_KEY DESC
> ROWS N;
>
> where N would be the number of rows to retrieve. You also have:
>
> SELECT *
> FROM T
> ORDER BY YOUR_KEY DESC
> ROWS N TO M;
>
> which will retrieve records N, N+1, N+2, ..., M. Useful for pagination
> and not supported, as far as I know, by the Firebird specific syntax.

The SQL standard (SQL:2008 and higher) clause is [FETCH][1] which was
added in Firebird 3. ROWS is also a Firebird specific invention, or it
might have been something that was in a SQL standard draft, but never
made it in a final standard.

The SQL Standard OFFSET/FETCH combination is IMHO better, though the
same result can be achieved with the non-standard FIRST and SKIP.

[1]:
https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/bk02ch09s06.html#rnfb30-dml-offsetfetch