CODEWITHSUNDEEP
phpsqllaravel
newLearner
newLearner

Reputation: 45

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

╔════╦═══════╦═══════╦═══════╗
║ 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.

Upvotes: 1

Views: 816

Answers (3)

pmiranda
pmiranda

Reputation: 8420

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);

enter image description here

Upvotes: 0

sssurii
sssurii

Reputation: 830

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. :)

Upvotes: 1

dougp
dougp

Reputation: 3087

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.

Upvotes: 0

Related Questions