Skip to content
Advertisement

SQL selecting most recent row inside join


I have 2 tables companies and invoices
I want to select all companies with their most recent invoice price.
I don’t seem to get it working.
This is what I tried:

SELECT *
FROM companies H INNER JOIN
     invoices V
     ON H.company_id = V.BC_ID
WHERE V.ISCOMMISSIE = 0 AND
      V.DATE = (SELECT MAX(v2.DATE) FROM invoices v2 WHERE v2.BC_ID = V.BC_ID AND v2.ISCOMMISSIE = 0);

But the query loads very long and I don’t know why. The structure looks like this:

companies

company_id | company_name |
   1       |   company 1  |
   2       |   company 2  | 

invoices

invoice_id | BC_ID | DATE       | ISCOMMISSIE | price     |
  1        |   2   | 2020-01-01 |      0      |   340,40  |
  2        |   1   | 2020-01-11 |      0      |   240,40  |
  3        |   1   | 2020-01-08 |      0      |   250,30  |
  4        |   2   | 2020-01-18 |      0      |   150,30  |
  5        |   2   | 2020-01-19 |      1      |   150,30  |

The BC_ID is the same as the company_id and ISCOMMISSIE should be 0.
I want to select the most recent date.
Does someone have an idea on how to do this and also make the query as fast as possible? http://sqlfiddle.com/#!9/2fc3a/1

Advertisement

Answer

Try:

SELECT H.*, V.*
FROM companies H 
INNER JOIN invoices V ON H.company_id = V.BC_ID
INNER JOIN ( SELECT v2.BC_ID, MAX(v2.DATE) DATE
             FROM invoices v2
             WHERE v2.ISCOMMISSIE = 0
             GROUP BY v2.BC_ID ) v3 ON v.BC_ID = v3.BC_ID 
                                   AND v.DATE = v3.DATE
AND V.ISCOMMISSIE = 0

And the index invoices (ISCOMMISSIE, BC_ID, DATE) may help…

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement