Select second highest value per distinct foreign key

Question

So, I have two tables, account and invoice, they are linked by the primary key from the account table ie. account.key and invoice.key.

I want to select account.accountnumber, invoice.invoicedate, invoice.invoiceamount for the second latest invoicedate from each account.

Any ideas?

So to select all invoices and their corresponding account numbers:

select a.accountnumber, i.invoicedate, i.invoiceamount
from account a
join invoice i on (a.key = i.key)

And to select the second latest invoice from the entire invoice table:

select MAX(invoicedate) from INVOICE i where invoicedate NOT IN (SELECT MAX(invoicedate) from i

But how do I get the second latest invoice, per account from the invoice table, along with the account number from the account table?

Thanks in advance.

Answer 1

By using the ROW_NUMBER() windowing function...

select accountnumber, invoicedate, invoiceamount 
from 
(
    select a.accountnumber, i.invoicedate, i.invoiceamount, 
        row_number() over (partition by a.accountnumber order by invoicedate desc) rn
    from account a 
        join invoice i on a.[key] = i.[key]
) v
where rn = 2