How to get rows where a column has highest value and another column has a specific value in laravel query builder

Question

╔════╦═══════╦═══════╦═══════╗
║ id ║ Col A ║ Col B ║ Col C ║
╠════╬═══════╬═══════╬═══════╣
║ 1  ║ 36    ║ 50    ║ AAA   ║
║ 2  ║ 36    ║ 50    ║ BBB   ║
║ 3  ║ 36    ║ 44    ║ CCC   ║
║ 4  ║ 36    ║ 44    ║ DDD   ║
║ 5  ║ 56    ║ 33    ║ EEE   ║
║ 6  ║ 56    ║ 78    ║ FFF   ║
╚════╩═══════╩═══════╩═══════╝

How to use laravel query builder to get table rows where highest number in 'Col B' and in same value 'Col A'?

In the end, I hope to get the result like:

╔════╦═══════╦═══════╦═══════╗
║ id ║ Col A ║ Col B ║ Col C ║
╠════╬═══════╬═══════╬═══════╣
║ 1  ║ 36    ║ 50    ║ AAA   ║
║ 2  ║ 36    ║ 50    ║ BBB   ║
║ 6  ║ 56    ║ 78    ║ FFF   ║
╚════╩═══════╩═══════╩═══════╝

the reason why getting these row is because in 'Col A' have two number 36 & 56. The highest number in 'Col B' is 50 for 36, and The highest number in 'Col B' is 78 for 56.

i don't understand your problem?? please say what you try to do.In B column highest number is 78.You try to get this ??or here highest appearance number is 50 & 44 in B column.Both two times..which one you want to get? — albus_severus, Nov 13 '19 at 14:39
You'll need to use a subquery. There's a [post about this](https://stackoverflow.com/questions/24823915/how-to-select-from-subquery-using-laravel-query-builder). — dougp, Nov 13 '19 at 16:52

score 1 · Accepted Answer · answered Nov 13 '19 at 17:15

Here is SQL query to fulfill your requirement:

SELECT * FROM `test` WHERE col_b in (SELECT MAX(col_b) from test GROUP BY col_a);

Consider test as your table name, col_a and col_b as Col A and Col B respectively.

Below is Laravel Query builder version of the above query:

Test::select('*')
        ->whereIn('col_b', function($query)
        {
           $query->from('test')->select(DB::raw("MAX(col_b)"))->groupBy('col_a');
        })->get();

Hope it works for you. :)

score 0 · Answer 2 · answered Nov 13 '19 at 16:57

I am uncertain if you need help with Laravel Query Builder or developing the query logic. Here is the query logic that will work in SQL Server. The concepts apply across all RDBMSs.

select a.id
, a.[Col A]
, a.[Col B]
, a.[Col C]
from TblA a
  inner join (
    select [Col A]
    , max([Col B]) as 'Col B'
    from TblA
    group by [Col A]
  ) b on b.[Col A] = a.[Col A]
     and b.[Col B] = a.[Col B]

Then you can use the correct answer from another post to convert that to use in Laravel Query Builder.

score 0 · Answer 3 · answered Nov 13 '19 at 18:07

Try this:

$result = DB::table('your_table')
        ->whereIn('your_table.col_b', function($query) {
            $query->from('your_table')
                  ->select(DB::raw("MAX(col_b)"))
                  ->groupBy('col_a');
        })->get();

dd($result);

How to get rows where a column has highest value and another column has a specific value in laravel query builder

3 Answers3