--- In firebird-support@yahoogroups.com, W O <sistemas2000profesional@...> wrote:

>
> 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.
>
> Walter.
>

If the relation between MOVIMCAB and MOVIMDET is one to one:

<sql>
select first 1 MOVIMDET.Product_Count
from MOVIMCAB
left join MOVIMDET on (MOVIMCAB.Movim_ID=MOVIMDET.Movim_ID)
order by MOVIMCAB.Movim_Date desc
</sql>

But, if it is possible than are several lines of detail for one line of MOVICAB:

<sql>
select first 1 sum(MOVIMDET.Product_Count)
from MOVIMCAB
left join MOVIMDET on (MOVIMCAB.Movim_ID=MOVIMDET.Movim_ID)
group by MOVIMCAB.Movim_Date
order by MOVIMCAB.Movim_Date desc
</sql>


José Manuel Sanjuán.