I have a table of data
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.