I added the KeyID so I could sort from newest to oldest. Was it necessary to add this KeyID, or is there another way to find the newest records without the KeyID?
On Fri, Dec 13, 2019 at 2:05 AM Mark Rotteveel m...@lawinegevaar.nl [firebird-support] <firebird-support@yahoogroups.com> wrote: > > > On 2019-12-13 08:14, Kjell Rilbe kjell.ri...@marknadsinformation.se > [firebird-support] wrote: > > Den 2019-12-12 kl. 22:36, skrev Clyde Eisenbeis cte...@gmail.com > > [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 > >
