Get the latest row each group based on max date

Question

I am trying to select single from every group where date is max.

I am trying to do with this bellow

|NO | effective   | price     |  level_one| level_two
+---+-------------+----------+|+++++++++++|++++++++++++
|1  | 2011-12-01  |  34       |     1     |    2
|2  | 2011-16-01  |  34       |     1     |    2
|3  | 2011-18-01  | 3434      |     1     |    2
|4  | 2011-16-01  | 3554      |     1     |    3

Result should be

|NO | effective   | price     |  level_one| level_two
+---+-------------+----------+|+++++++++++|++++++++++++
|3  | 2011-18-01  | 3434      |     1     |    2
|4  | 2011-16-01  | 3554      |     1     |    3

But result come

|NO | effective   | price     |  level_one| level_two
+---+-------------+----------+|+++++++++++|++++++++++++
|3  | 2011-12-01  | 34        |     1     |    2
|4  | 2011-16-01  | 3554      |     1     |    3

tried with

$price = App\Price::with('others')
    ->orderBy('effective', 'Desc')
    ->groupBy('level_one','level_two')
    ->get();

This [would make a good research read](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) may even be considered a Dup — RiggsFolly, Apr 28 '20 at 16:49
Does `App\Price::groupBy('level_one', 'level_two')->latest('effective')->first()` return correct date? — Tpojka, Apr 28 '20 at 17:23
It means orderBy comes after groupBy has been made. Subquery is needed. — Tpojka, Apr 28 '20 at 17:42
@codewarrior I don't know how to do this using eloquent, but I know it's possible using raw SQL. You have to have a nested `SELECT` statement and select from that, something like this: `SELECT level_two, MAX(price) FROM (SELECT level_two, price FROM your_table) AS der GROUP BY level_two` — Gharbad The Weak, Apr 28 '20 at 19:40

score 0 · Answer 1 · answered May 05 '20 at 22:45

0

Problem on your date format. Normally eloquent and mysql use Y-m-d date format.

Use like this:

$price = App\Price::with('others')
    ->orderByRaw("DATE_FORMAT(effective,'%Y-%d-%m') DESC")
    ->groupBy('level_one','level_two')
    ->get();

answered May 05 '20 at 22:45

Omer YILMAZ

1,234
1
7
15

Get the latest row each group based on max date

1 Answers1