CODEWITHSUNDEEP
mysqlsqlgroup-by
CE_
CE_

Reputation: 1118

Get most recent ROW with a GROUP BY on multiple columns

I'm trying to get the most resent result for every amount linked to a duration. This is my table :

 SELECT * FROM financement;

 id    amount   duration   total
 8       200     5       result8
 7       200     4       result7 
 6       100     5       result6
 5       100     4       result5 
 4       200     5       result4
 3       200     4       result3
 2       100     5       result2
 1       100     4       result1

There is is a total for every amount linked to a duration. My problematic is that, they can be multiple row for the same amout x duration and I only want the most recent. For exemple in my case :

id    amount   duration   total
5       100     4       result5 
1       100     4       result1

I tried to use a GROUP BY like this:

SELECT * FROM `financement` GROUP BY amout, duration ORDER BY `id` DESC

But with this method, even with the ORDER BY id DESC, I still get the most ancien ROW. I also tried to SELECT max() in a sub query like this, but the query is extremely long and it times out.

SELECT * financement where id in (select max(id) from financement group by amount, duration);

How can I get this output ?

id    amount   duration   total
8       200     5       result8
7       200     4       result7 
6       100     5       result6
5       100     4       result5

Upvotes: 2

Views: 57

Answers (3)

NorthernDev
NorthernDev

Reputation: 339

This should work:

SELECT * FROM `financement` 
WHERE id IN (
    SELECT max(id) as id FROM `financement` GROUP BY duration, amount
)
ORDER BY `id` DESC

Upvotes: 1

Farhana
Farhana

Reputation: 11

Try this query:

SELECT * FROM financement ORDER BY id DESC

output of this query

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271141

I think you want filtering. You can use a scalar subquery:

select f.*
from financement f
where f.id = (select max(f2.id)
              from financement f2
              where f2.amount = f.amount and f2.duration = f.duration
             );

Upvotes: 0

Related Questions