Get the latest values ​​in GROUP BY using Laravel Rloquent ORM

Question

I am trying to understand how the Laravel Eloquent ORM works, and looked at the following MySQL query:

SELECT id, name, date FROM tablename GROUP BY name ORDER BY date 

Using GROUP BY always returns the oldest value of name . Is there any way to return the last value?

Answer 1

Try the following code,

 Tablename::select('id', 'name', DB::raw('max(date) as latest_date')) ->groupBy('name') ->orderBy('latest_date') ->get() 

Answer 2

Here's a working SQL query that only gets the latest results that are unique to what you selected with a.id = b.id - if you want to make unique names, then it will be a.name = b.name .

 SELECT DISTINCT a.* FROM tablename a LEFT JOIN tablename b ON a.id = b.id AND a.date < b.date WHERE b.date IS NULL