Oracle SELECT FIRST?

Question

I have a table of data invoices

invoice | vendor | date
0       | 0      | 08/01/2018
1       | 0      | 08/02/2018
2       | 1      | 08/05/2018
3       | 2      | 08/10/2018
4       | 2      | 08/10/2018
5       | 2      | 08/09/2018

I am trying to select the latest invoice number from each vendor, when there are more than one invoice for the same day I need the largest invoice number.

This is what I'm trying, but it gives me SQL Error: ORA-00979: not a GROUP BY expression sql developer references the beginning of first_value with that error.

SELECT max(date), first_value(invoice) OVER (order by date, invoice)
FROM invoices
GROUP BY vendor

I am using max(date) instead of max(invoice) because the largest invoice isn't always the latest date.

EDIT: this is what I'm trying to select

invoice | vendor | date
1       | 0      | 08/02/2018
2       | 1      | 08/05/2018
4       | 2      | 08/10/2018

EDIT2: added another row to demonstrate that the largest invoice number isn't always the latest date.

Answer 1

If I understand you correctly you can enumerate the invoices per vendor and pick the last one (order by desc):

with invoices(invoice, vendor, cdate) as
      (select 0, 0, date '2018-01-08' from dual union all
       select 1, 0, date '2018-08-02' from dual union all
       select 2, 1, date '2018-08-05' from dual union all
       select 3, 2, date '2018-08-10' from dual union all
       select 4, 2, date '2018-08-10' from dual
      )
      select invoice, vendor, cdate
      from (
        select invoice, vendor, cdate
             , row_number() over (partition by vendor 
                                  order by date desc, invoice desc) as rn
        from invoices
      ) t
      where rn = 1;