CODEWITHSUNDEEP
sql-server
Farellia
Farellia

Reputation: 187

SQL Server: return the max of a group

Assume I have the following table like so:

Parent_Company   Child_Company   Requested    Authorized   Char1   Char2   
------------------------------------------------------------------------
Fruits              Apples         $1000       $1000         A       10   
Fruits              Apples         $10000      $5000         E       10  
Fruits              Apples         $7000       $7000         C       15  
Fruits              Pears          $1000       $0            B       20  
Fruits              Grapes         $1000       $200          A       10  
Vegetables          Celery         $500        $500          D       17
Vegetables          Celery         $1000       $1000         A       10
Vegetables          Lettuce        $1000       $1000         A       10
Vegetables          Lettuce        $2000       $0            B       12

I want my query to return:

Parent_Company   Child_Company   Requested    Authorized   Char1   Char2   
------------------------------------------------------------------------
Fruits              Apples         $7000       $7000         C       15  
Fruits              Pears          $1000       $0            B       20 
Fruits              Grapes         $1000       $200          A       10  
Vegetables          Celery         $1000       $1000         A       10
Vegetables          Lettuce        $1000       $1000         A       10

The logic here is that based on different Parent/Child combinations, I want it to return the row with the highest Authorized amount.

The first thought that came to mind is that I can simply use a Max + Group By query:

   SELECT 
       Parent_company, Child_Company, Char1, Char2, Requested, 
       Max(Authorized)
   FROM
       Food
   GROUP BY 
       Parent_Company, Child_Company, Char1, Char2, Requested

But it's not really working the way I want to since Char1, Char2 and Requested amounts are changing.

From my limited knowledge of SQL Server, I think that maybe something with PARTITION BY can be used, but I really don't know how to approach it with this.

Help would be greatly appreciated.

Upvotes: 1

Views: 27

Answers (1)

jpw
jpw

Reputation: 44871

You're on the right track with partition. You can use the row_number function and partition by [Parent_Company], [Child_Company] to number the data and then filter out the top rows:

select * from (
 select *, row_number() over (partition by [Parent_Company], [Child_Company] 
                              order by [Authorized] desc) as row_number
 from your_table
) as table_alias
where row_number = 1

Upvotes: 2

Related Questions