>Hello everybody
>
>I have a table MOVIMCAB (Movim_ID, Movim_Date)
>
>and a table MOVIMDET (Movim_ID, Product_Code, Product_Count)
>
>both tables can be relationated by the Movim_ID column
>
>How can I to know which is the Product_Count of the last date?
>
>MOVIMCAB
>------------------------
>1 01/01/2010
>2 02/02/2010
>3 06/03/2010
>
>MOVIMDET
>-----------------------
>1 501 20
>2 501 12
>3 501 15
>
>I want to get 15, because it is the Product_Count of the last date.
>
>What I could to write?
>
>Thanks in advance.

Hi Walter!

This can be solved several ways, you've already been shown one possibility by Norman. Three other options are (there are probably more, although they are likely to be more complicated):

a)
SELECT MD.Product_Count
FROM MOVIMDET MD
JOIN MOVIMCAB MC on MC.Movim_ID = MD.Movim_ID
LEFT JOIN MOVIMCAB MC2 on MC.MovimDate < MC2.MovimDate
WHERE MC2.Movim_ID IS NULL

b)
SELECT MD.Product_Count
FROM MOVIMDET MD
JOIN MOVIMCAB MC on MC.Movim_ID = MD.Movim_ID
WHERE NOT EXISTS(SELECT * from MOVIMCAB MC2 where MC.MovimDate < MC2.MovimDate)

c)
WITH MaxDate(Movim_Date) as
(SELECT MAX(Movim_Date)
FROM MOVIMCAB)

SELECT MD.Product_Count
FROM MaxDate MaxD
JOIN MOVIMCAB MC on MaxD.MovimDate = MC.MovimDate
JOIN MOVIMDET MD on MC.Movim_ID = MD.Movim_ID

Option a) and b) are just two ways of saying the same thing and should work on any Firebird version, whereas option c) requires a bit more modern version (I think Firebird 2.1, but may be wrong)

HTH,
Set