0

I have attempts table with following data:

ID | ATTEMPT_NO | LESSON_ID | TEST_ID | VALID
---------------------------------------------------------
76 |    123     |     3     |    4    |   0
77 |    123     |     3     |    9    |   1

I need to group data by attempt_no, and if possible get latest value from group.

My code:

Attempts::groupBy('attempt_no')->orderBy('id', 'desc')->paginate(10);

Problem is, groupBy takes first found record. If:

ID: 76; VALID = 1;

ID: 77; VALID = 0;

// query returns valid true

Hovewer if

ID: 76; VALID = 0;

ID: 77; VALID = 1;

// query returns valid false;

I tried all possible combinations including:

Attempts::groupBy('attempt_no')->orderBy('valid', 'desc')->orderBy('id', 'desc')->paginate(10); // not what I need

Attempts::orderBy('valid', 'desc')->groupBy('attempt_no')->orderBy('id', 'desc')->paginate(10); // not what I need

But any of my attempt returns result which I need, that's why I am asking here for help.

UPDATE: Solved by myself, maybe somebody will need it:

$my_solutions = Attempts::whereRaw('id = (SELECT max(id) FROM attempts a2 WHERE a2.attempt_no = attempts.attempt_no)')
            ->groupBy('attempt_no')
            ->orderBy('id', 'desc')
            ->paginate(10);
EvaldasL
  • 31
  • 3
  • 1
    Welcome to SO. Glad you solved it, if you have an answer, [post it as an answer](https://stackoverflow.com/help/self-answer). There are many similar questions here already, ([example](https://stackoverflow.com/questions/31857961/order-by-before-group-by-using-eloquent-laravel)) if they answer your question, consider [closing this one as a duplicate](https://stackoverflow.com/help/duplicates). – Don't Panic May 02 '23 at 07:34

0 Answers0