On 2019-12-13 08:14, Kjell Rilbe [email protected] [firebird-support] wrote: > Den 2019-12-12 kl. 22:36, skrev Clyde Eisenbeis [email protected] > [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
