CODEWITHSUNDEEP
mysqldatabasegreatest-n-per-group
Ibrahim Azhar Armar
Ibrahim Azhar Armar

Reputation: 25745

Fetch rows with MAX DATE and GROUP BY

I have a table name payment_schedule with following contents

payment_schedule

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

enter image description here

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.

enter image description here

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.

Upvotes: 4

Views: 1150

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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;

Upvotes: 0

You Old Fool
You Old Fool

Reputation: 22941

This is called Group-wise Maximum and tagged here as . 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

Upvotes: 4

SIDU
SIDU

Reputation: 2278

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

Upvotes: 2

Related Questions