How to get last one record from multiple rows-CodePudding

I have a table called test_series_records Where one users have multple rows, I want the last created record per user, Like if one user has 4 records in the table, then the last or recent record should select. I want to show all the records available in the table but the last record per use. How can I achive that?

I have tried this-

$result = TestSeriesRecord::groupBy('user_id')
->where('test_id', $id)
->orderBy('correct_answers', 'DESC')
->get();

But It's give an error

SQLSTATE[42000]: Syntax error or access violation: 1055 'scordemy_db.test_series_records.total_questions' isn't in GROUP BY

CodePudding user response：

You can try this hope it will work. Creating a subquery and running it before, so that results are ordered as expected and grouped after.

$sub = TestSeriesRecord::orderBy('correct_answers','DESC');

$result = DB::table(DB::raw("({$sub->toSql()}) as sub"))
->where('test_id',$id)
->groupBy('user_id')
->get();

CodePudding user response：

Try this latest(); method: https://laravel.com/docs/9.x/queries#latest-oldest

$result = TestSeriesRecord::where('test_id', $id)->latest()->first();

And Remember this will only work when have default created_at column in your table