How to get last record of a group?

Hi there, I came across with a problem and stuck on it , I want to get the last record in a group of value. For example :

I have a table called 'flight_times' , the table has those columns respectively : id, aircraft_id , departure, arrival ,flight_time, effective_from. I want to group that table by departure and arrival , then want to get the records that has the latest effective_from value. When I try to use groupBy statement , it always gives the first record . That is a big problem for me. Any helps ?

Thanks.

tisuchi

9 years ago

Level 70

Try this-


$latest  = DB::table('tableName')
                                ->where('fieldName',  'conditionValue')
                                ->groupBy('fieldName')
                                ->orderBy('filedName', 'Desc')
                                ->first();

EliasSoares

9 years ago

Level 10

If you have timestamps on your model you can use: $model->latest()

skyline

9 years ago

Level 4

No guys , sorry but those examples doesn't work. An example of my dataset :

|1 |AAL |VXO | 233 |2016-06-28|

|2 |AAL |MAN | 59 |2013-04-22|

|3 |AAL | VXO |240 |2016-07-23 |

|4 |AAL |VXO |250 |2016-08-01 |

In that dataset, the 4th record should be the parent of 1st and 3th .

d3xt3r

9 years ago

Level 29

This is not possible without sub-queries. To get a hang of it, try writing a sql query first, that will help you convert it to eloquent.

FrancescoZaffaroni

9 years ago

Level 6

SELECT departure, arrival, max(effective_from)from flights GROUP BY arrival, departure;

This in postgreSQL return

https://i.sli.mg/5M7LCb.png

I don't know if that's exactly what you want but it's the closest I could get. Idk how to get also the ID

skyline

9 years ago

Best Answer

Level 4

This query does the solution !

select * from (select * from flights ORDER BY effective_from DESC) AS x GROUP BY departure,arrival

Thanks.

1 like

club21th

1 year ago

Level 8

I tried but doesn't work.

Please or to participate in this conversation.