Is <timestamp> a field I create, or is it part of all records? On Fri, Dec 13, 2019 at 12:04 PM Mark Rotteveel m...@lawinegevaar.nl [firebird-support] <firebird-support@yahoogroups.com> wrote:
> > > On 13/12/2019 16:48, Clyde Eisenbeis cte...@gmail.com [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 > >
