CODEWITHSUNDEEP
phplaraveljoineloquent
Rey Norbert Besmonte
Rey Norbert Besmonte

Reputation: 791

Laravel - sub query max of GROUP BY

I need help in getting the max claim_id of a table that is grouped by claim_company_id in Laravel.

Here is the table sample:

claim_id         claim_company_id       Date
1                     1                  1/1/2010
2                     1                  1/2/2010
3                     7                  1/3/2010
4                     7                  1/4/2010
5                     7                  1/5/2010

SELECT * FROM `claims`
WHERE
claims.claim_id in (SELECT max(claim_id) from claims GROUP by claim_company_id)

The output should be

2 - 1 - 1/2/2010
5 - 7 - 1/5/2010

But when I run this query

$query = DB::table('claims')
    ->groupBy('claim_company_id')
    ->get([
        'claims.*',
        'claim_company_id',
        DB::raw('MAX(claim_id) as claim_id_new')
    ]);

The result is

1 - 1 - 1/1/2010
3 - 7 - 1/3/2010

I’m stuck for a day now. Any ideas?

Upvotes: 5

Views: 10098

Answers (1)

Rey Norbert Besmonte
Rey Norbert Besmonte

Reputation: 791

So I needed to use whereRaw to run the code but turns out it works this way. Answering for future reference

$query = DB::table('claims') 
             ->whereRaw('claim_id in (select max(claim_id) from claims group by (claim_company_id))')
             ->get();

Upvotes: 9

Related Questions