Databases - Firebird SQL to Return the Most Recent Change

Databases - Firebird
SQL to Return the Most Recent Change

Many databases store records associated with a date and time.

But how do you find

The "most recent" record
The "most recent" record for client abc
The "most recent" record for all the clients

Using multiple queries and a program, there are many ways to perform this type of data retrieval - but I want to do this with a single query.

This is one of those areas where different databases implement the function in different ways.

Simple "group by"

This is a basic query that will return the most recent date for each category ("Name" in this example).

select "Name", max("Date_Time") from  "Programs_tbl"
group by "Name"

But it can not be directly extended to include other fields.

Let the "where" Clause Contain a Second Query

This example works - but it appears to be pretty inefficient

select * from "Programs_tbl"  xx
where xx."Date_Time" = (select max("Date_Time") from  "Programs_tbl" yy
                        where  xx."Name" = yy."Name")

Notice that because the same table is used in both queries, the 2 result sets must be assigned aliases - xx and yy in this example.

Place the Second Query in a "Join"

I found SQL: MAX Function help for Oracle - however, this example does not work with Firebird. I tried various types of joins, but I was never able to get Firebird to accept a select statement as the second table.

SELECT rh.user_name, rh.report_name, rh.report_run_date

FROM report_history rh,
   (SELECT max(report_run_date) as maxdate, report_name
     FROM report_history
     GROUP BY report_name) maxresults

WHERE rh.report_name     = maxresults.report_name
AND   rh.report_run_date = maxresults.maxdate;

According to Introduction to PostgreSQL SQL - Nested subqueries and derived tables, this imbedded query is known as either a derived table or a temporary view.

Author: Robert Clemenzi - clemenzi@cpcug.org