Select Distinct and order by occurrence in Laravel-CodePudding

Good day. I have a table as shown in the image below. Using Eloquent model (Query builder too will do) I want to be able to select only unique cases with the suit number, and at the same time order by the case that have the highest occurrence down to the lowest occurrence.

For example, in this image below, since "CA/E/14/2011" has occurrence of 3,I only want to select a distinct value, hence my query should return two cases. "CA/E/14/2011" and "CA/A/344/2015" but "CA/E/14/2011" should come first because it has higher occurrences.

CodePudding user response：

The below query should return the desired result set.

SELECT COUNT(suit_number) as occurrence, suit_number
FROM records
GROUP BY suit_number
ORDER BY occurrence DESC;

Eloquent:

use App\Models\Record;

Record::selectRaw("COUNT(suit_number) as occurence, suit_number")
    ->groupBy("suit_number")
    ->orderBy("occurence", "desc")
    ->get();

SQL Fiddle

If you only want the suit_number you can do

Record::selectRaw("COUNT(suit_number) as occurence, suit_number")
        ->groupBy("suit_number")
        ->orderBy("occurence", "desc")
        ->pluck('suit_number');