CODEWITHSUNDEEP
phpsqllaravel
Koeno
Koeno

Reputation: 1573

Order by per group (Query Builder)

I have an user and a score table. Each user has multiple scores, for example:

Only the highest score for each user is relevant. What I want to achieve is to get the 10 users that have the highest score. This works with the following laravel query:

    $results = Score::orderBy('score', 'desc')
        ->groupBy('user_id')
        ->limit(10)
        ->get()

The problem with this query is that it returns the top users, but not their highest score. It returns the first score in the group. So when I query over the given example, this will return:

How can I make sure that it returns the top users with their highest score? I tried to ->distinct('score') but that didn't work.

Upvotes: 2

Views: 1024

Answers (3)

Iman Kazemi
Iman Kazemi

Reputation: 552

-- i don't know well querybuilder but this query solve the problem

select top 10 * 
from 
(select userID, max(score) score from yourTable group by userID) K
order by score desc

Upvotes: 0

ArtisanBay
ArtisanBay

Reputation: 1041

Try this: (Query Builder)

Syntax:

DB::table('name')->orderBy('column')->groupBy('column')->get();

Modify your query:

$results = DB::table('score')
    ->orderBy('score', 'desc')
    ->groupBy('user_id')
    ->limit(10)
    ->get();

Hope this is helpful.

Upvotes: 0

yudijohn
yudijohn

Reputation: 1278

try this

$results = Score::select("*", DB::raw("MAX(score) as max")
    ->groupBy('user_id')
    ->orderBy('max', 'desc')
    ->limit(10)
    ->get();

Upvotes: 2

Related Questions