Fabiano Bonin wrote:

> Hi all,
>
> Look at this query. I want to get the last invoice of each customer:
>
> select
> ( select first 1 id from invoices inv where inv.customer_id =
> cus.customer_id order by inv.id desc )
> ( select first 1 inv_date from invoices inv where inv.customer_id =
> cus.customer_id order by inv.id desc )
> ( select first 1 number from invoices inv where inv.customer_id =
> cus.customer_id order by inv.id desc )
> ( select first 1 ship_date from invoices inv where inv.customer_id =
> cus.customer_id order by inv.id desc )
> from
> customers cus
>
> Is there a way to get the same result as the query above, but
> introducing the table "invoices" in the "from" clause and avoiding
> these subselects?
>
> Something like this:
>
> select
> inv.id,
> inv.date,
> inv.number,
> inv.ship_date
> from
> customers cus
> inner join *** insert invoices here somehow to return only the last
> invoce for each customer ***
>
> Regards,
>
> Fabiano.
>
>

select
*
from
Customers cus join
Invoices Inv on (inv.customer_id = cus.customer_id)
where
not exists (select 1 from Invoices where Invoices.Customer_ID =
cus.CustomerID and Invoices.Invoice_ID > inv.Invoice_ID)

see you !


--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br