> 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?

Since you're running v2+

You can do:

select
inv.id,
inv.date,
inv.number,
inv.ship_date
from
customers cus
JOIN (
select First 1
id,
date,
number,
ship_date
from invoices
where
customer_id = cus.customer_id
order by id desc
) as Inv on 1 = 1


Sean