Subject Re: Subquery Question (FB 1.5)
Author Svein Erling
In Firebird 2.1 your way of thinking would use a common table expression (CTE) for this:

WITH MyCTE(inventory_pk, max_of_wl_date) as
(SELECT inventory_pk, max( w.wl_date )
from watlev
group by inventory_pk)

SELECT w.inventory_pk, w.wl_date, w.wl_comment
from watlev w
join MyCTE m
on w.inventory_pk = m.inventory_pk
and w.wl_date = m.max_of_wl_date

Though an equally simple way to write the same thing (assuming wl_date is never NULL) that works with any Firebird version is:

SELECT w.inventory_pk, w.wl_date, w.wl_comment
from watlev w
where not exists(select *
from watlev w2
where w.inventory_pk = w2.inventory_pk
and w.wl_date < w2.wl_date)

CTEs are great for more complex situations, e.g. when you need recursion or prefer the logic to look more 'modularised'.


--- In, "data.inspector" <data.inspector@...> wrote:
> Here are the two peices of the query I can't get tied together.
> Thanks for looking at it.
> select
> watlev.inventory_pk,
> watlev.wl_date,
> watlev.wl_comment
> from
> watlev
> where
> /*It is this piece of the SQL Statement I can't seem to puzzle out */
> (select
> w.inventory_pk,
> max( w.wl_date ) max_of_wl_date
> from watlev w
> group by w.inventory_pk)