On 13/12/2019 16:48, Clyde Eisenbeis [email protected] [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
