Very tricky :-) I would never imagine this. Thank you!

Just for curiosity, do you figure out some way to do the same thing if i
can't use the ID?
for instance, if i want to order by invoice date desc and the table can have
duplicate dates for the same customer?

Regards,

Fabiano.

On 7/17/07, Alexandre Benson Smith <iblist@...> wrote:
>
> 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
>
>
>


[Non-text portions of this message have been removed]