>hello,
>
>how to do this :
>
>select
> Id_contact,
> (select First 1 skip 0 Amount, currency from invoice where ID_invoice=id_contact and ...) as amount_last_invoice, >currency_last_invoice
>
>from
> contact
>
>off course i can do 2 time (Select Amount ...) as amount_last_invoice, and (Select Currency ...) as currency_last_invoice ... >but i think their is a better way no ?

Not sure if it is any better when you only need two fields, but you could, of course, use a CTE:

WITH TMP_INVOICE(ID_INVOICE, AMOUNT_LAST_INVOICE, CURRENCY_LAST_INVOICE) AS
(SELECT I.ID_INVOICE, I.AMOUNT, I.CURRENCY
FROM INVOICE I
LEFT JOIN INVOCE I2 ON I.ID_INVOICE = I2.ID_INVOICE
AND I.SomeUniqueField < I2.SomeUniqueField ...
WHERE I2.ID_INVOICE IS NULL)

SELECT C.ID_CONTACT, I.AMOUNT_LAST_INVOICE, I.CURRENCY_LAST_INVOICE
FROM CONTACT C
LEFT JOIN TMP_INVOICE I ON C.ID_CONTACT = I.ID_INVOICE

As an alternative to the LEFT JOIN within the CTE, you could use NOT EXISTS, in cases like this it is basically just a matter of taste what you prefer. I guess other solutions could be used if you wanted more than one row, but you neither asked for that, nor provided any ORDER BY, so I'll leave that for later.

By the way, I'm pretty sure I've read on this list (albeit long ago) that using two or more subselects with the same where clause only makes the query look verbose, it should be almost as quick to execute regardless of whether you selected only one field this way or many fields (though all subselects have to use the same join and where clauses, two subselects that have different where clauses would both add to the execution time).

HTH,
Set