On Fri, Dec 13, 2019 at 12:04 PM Mark Rotteveel mark@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

 

On 13/12/2019 16:48, Clyde Eisenbeis cte677@... [firebird-support]
wrote:
> One table contains history.  The old history is never replaced.  New
> history is added.  What is relevant is the most recent history. Is there
> a way to find the newest records without the KeyID?

Yes, you do

SELECT *
FROM <table>
ORDER BY <timestamp> DESC
FETCH NEXT 10 ROWS ONLY

Where <table> is the table in question, and <timestamp> is a suitable
column (eg a timestamp) to determine what the newest record is.

If you want that result in ascending order of time, then you need to add
another order by:

SELECT *
FROM (
SELECT *
FROM <table>
ORDER BY <timestamp> DESC
FETCH NEXT 10 ROWS ONLY
) a
ORDER BY <timestamp> ASC

Or you can use ROW_NUMBER, although that is probably less efficient:

SELECT *
FROM (
SELECT <table>.*,
ROW_NUMBER OVER (ORDER BY <timestamp> DESC) AS ROWNR
FROM <table>
) a
ORDER BY <timestamp> ASC
WHERE ROWNR < 10

Mark
--
Mark Rotteveel