Fetch rows with MAX DATE and GROUP BY

Question

I have a table name payment_schedule with following contents

I want to fetch records with MAX(due_date) GROUPED BY loan_application_id

With reference to records in above image, i expect the result to be following

I tried using following SQL query

SELECT
    id,
    MAX(due_date) as due_date,
    loan_application_id
FROM
    payment_schedule
GROUP BY
    loan_application_id

Which returns me the following result.

As you see it does not return the corresponding id for a given due date.

Additionally, I have another column called payment_type_id and I need to exclude rows when payment_type_id has value of 3.

I tried several solution available here, nothing seems to work, how to go about it?

Thanks.

Hi, how about this query? SELECT id, MAX(due_date) as due_date, loan_application_id FROM payment_schedule GROUP BY loan_application_id,id — M3ghana, Dec 15 '15 at 04:09

score 4 · Accepted Answer · edited May 23 '17 at 12:01

This is called Group-wise Maximum and tagged here as greatest-n-per-group. The most traditional approach is to find the value you want and do a join to get the corresponding row per group like this:

SELECT
    ps.id,
    ps.due_date,
    ps.loan_application_id
FROM
(
    SELECT
        MAX(due_date) as due_date,
        loan_application_id
    FROM payment_schedule
    WHERE payment_type_id != '3'
    GROUP BY loan_application_id
) ps2
    LEFT JOIN payment_schedule ps USING (loan_application_id)
WHERE ps.due_date = ps2.due_date
    AND ps.payment_type_id != '3'
GROUP BY ps.loan_application_id

It's also worth mentioning that this query will run a bazillion times faster if you have an index on your loan_application_id and due_date columns.

Best discussion I've seen here on SO is this: Select first row in each GROUP BY group?

Also addressed in the official docs here: http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html

i revised my answer to mention this, but make sure you have indexes on the relevant columns. it will make this a lot faster on large tables. — But those new buttons though.., Dec 15 '15 at 04:54

SIDU · Answer 2 · 2015-12-15T04:08:52.570

2

If due date per loan_application_id is distinct, you can remove the keyword distinct below:

select distinct a.*
from payment_schedule a, (
    select loan_application_id, max(due_date) max_date
    from payment_schedule
    where payment_type_id <> 3
    group by 1
) as b
where a.loan_application_id = b.loan_application_id
and a.due_date = b.max_date

edited Dec 15 '15 at 04:08

answered Dec 15 '15 at 04:01

SIDU

2,258
1
12
23

Thanks, this works, i forgot to add one more condition i.e i got another column called payment_type_id, i need to exclude rows when payment_type_id has value of 3 – Ibrahim Azhar Armar Dec 15 '15 at 04:06
OK updated. if your payment_type_id got null value: use this one: where ifnull(payment_type_id,0)<>3 – SIDU Dec 15 '15 at 04:09

Gordon Linoff · Answer 3 · 2015-12-17T03:25:47.500

0

In most databases, this is easiest using window functions. In MySQL, you can use a join and group by:

select ps.*
from payment_schedule ps join
     (select load_application_id, max(due_date) as maxdd
      from payment_schedule
      group by load_application_id
     ) l
     on ps.load_application_id = l.load_application_id and ps.due_date = l.maxdd;

edited Dec 17 '15 at 03:25

answered Dec 15 '15 at 03:41

Gordon Linoff

1,242,037
58
646
786

This gives me incorrect result, it returns 3 records, I have updated my question to reflect the result. – Ibrahim Azhar Armar Dec 15 '15 at 04:01
@IbrahimAzharArmar . . It was missing a join condition. – Gordon Linoff Dec 17 '15 at 03:25

Fetch rows with MAX DATE and GROUP BY

3 Answers3