0

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.

enter image description here

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.

Korlahwarleh
  • 143
  • 11

1 Answers1

2

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');
linktoahref
  • 7,812
  • 3
  • 29
  • 51
  • Thanks @linktoahref. Worked, but I discover that with that query, I am unable to get any other column in the table. apart from suit_number. Is that possible? – Korlahwarleh Sep 25 '21 at 20:04
  • I don't think that would be possible due to https://stackoverflow.com/q/41887460/5808894 – linktoahref Sep 26 '21 at 08:01
  • 1
    Thanks. I later did this and it served the purpose ```CaseView::distinct()->selectRaw("COUNT(suit_number) as occurrence, suit_number, court, case_title") ->groupBy('suit_number', 'court', 'case_title') ->orderBy('occurrence', 'desc') ->get();``` – Korlahwarleh Sep 26 '21 at 14:58