CODEWITHSUNDEEP
mysqlsqljoinmax
Webdeveloper_Jelle
Webdeveloper_Jelle

Reputation: 2856

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

Upvotes: 1

Views: 67

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Your query is fine:

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
               );

For performance, you want an index on invoices(BC_ID, ISCOMMISSIE, DATE).

A good alternative is to use window functions:

SELECT *
FROM companies H INNER JOIN
     (SELECT V.*,
             ROW_NUMBER() OVER (PARTITION BY BC_ID ORDER BY DATE DESC) as seqnum
      FROM invoices V
      WHERE V.ISCOMMISSIE = 0
     ) V
     ON H.company_id = V.BC_ID
WHERE seqnum = 1;

Upvotes: 1

Naveen Kumar
Naveen Kumar

Reputation: 2006

Another way to get the expected output:

select * from companies A join (
select * from invoices where (BC_ID,DATE) in(
select BC_ID as BC_ID, MAX(DATE) DATE from invoices where ISCOMMISSIE = 0 group by 
BC_ID
))B on A.company_id=B.BC_ID;

Upvotes: 0

Gosfly
Gosfly

Reputation: 1300

Depending on columns you need, you might not need to join with companies table. Also it is not needed to test for iscommissie = 0 two times, you can just test it one time in the subquery before joining.

See the query below : 

SELECT i.*
FROM invoices i
JOIN (
     SELECT i.bc_id, MAX(date) AS max_date
     FROM invoices i
     WHERE iscommissie = 0
     GROUP BY i.bc_id
) i_temp ON i.bc_id = i_temp.bc_id AND i.date = i_temp.max_date

FIND A DEMO HERE

Upvotes: 0

Akina
Akina

Reputation: 42632

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...

Upvotes: 1

Related Questions