Get a row which has the maximum id

Question

Problem in simple words, 1st must be left joined to the 2nd table where the record is latest. So, I use an approach of using function MAX()

Currently I have 2 tables.

matches

matches_payments

|

Now I want to join the second table to first one using MAX(id) on matches_payments

Desired result

but I am not getting desired result due to greatest-n-per-group problem.

Query

SELECT matches.id, mp.*
FROM matches
  LEFT JOIN (SELECT
               MAX(id) AS id,
               match_id
               paymentStatus
             FROM matches_payments
             GROUP BY match_id) AS mp ON mp.id = matches.id;

Desired result is not produced due to : Stackoverflow Question

When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same. FROM MySQL Dev

PS : I know the tables are poorly designed. It is not my work as the last developer did those.

I didn't vote, but maybe because you left out the MCVE that's required for questions like this? — Barmar, Feb 20 '17 at 09:27
@Barmar I did my best to explain :) thank you. I checked out the duplicate question. It answer my question to some extent. — Rooshan Akthar, Feb 20 '17 at 09:36
And it should be `ON mp.id = matches.id AND mp.match_id = matches.match_id`. — Barmar, Feb 20 '17 at 10:12
The query to get the row with the max ID in a group needs to be a join between the table and itself, not between two different tables. If you want to add information from another table, that's a second join. — Barmar, Feb 20 '17 at 10:15
@Barmar I need every matches because of that reason LEFT JOIN. If there is no equal matches_payment then client has not paid. `paymentStatus` is my main concern. I want Payment Status of each match. — Rooshan Akthar, Feb 20 '17 at 10:15
@Barmar LIMIT inside sub query is not supported. therefore I am out of options. — Rooshan Akthar, Feb 20 '17 at 10:17

score 1 · Accepted Answer · edited May 23 '17 at 12:00

1

You need two joins. You need a self-join of the matches_payments table to get the row with the highest ID for each match_id, as shown in SQL Select only rows with Max Value on a Column. Then you LEFT JOIN this to matches to combine the two tables:

SELECT m.*, mp.paymentStatus, mp.paymentAmount
FROM matches AS m
LEFT JOIN (
    SELECT mp1.*
    FROM matches_payments AS mp1
    JOIN (SELECT match_id, MAX(id) AS id
          FROM matches_payments
          GROUP BY match_id) AS mp2
    ON mp1.match_id = mp2.match_id AND mp1.id = mp2.id
) AS mp ON mp.match_id = m.id

edited May 23 '17 at 12:00

Community

1
1

answered Feb 20 '17 at 10:20

Barmar

741,623
53
500
612

thanks for taking time to explain and clarifying things :) – Rooshan Akthar Feb 20 '17 at 10:24

Get a row which has the maximum id

1 Answers1