SQL Case MAX WHEN LIKE

Question

I would like to create a case statement or a rank over statement for a particular outlier case I have.

I am not sure how to write a case statement utilizing CASE (pseudo code)

WHEN MAX Total_Revenue COMPANY like 'ABC%'
else COMPANY

i have tried rank over, but it is not working:

,RANK() OVER(COMPANY LIKE 'DEF%' ORDER BY  Total_Revenue DESC) AS grp

Current table:

Company    Total_Revenue
ABC        10
DEF1       25 --This row will NOT be selected as its less than
DEF2       35 -- this row will be kept    
GHI3       65
JKL9       100  

Ouput table:

Company    Total_Revenue
ABC        10
DEF2       35     --kept 
GHI3       65
JKL9       100  

Answer 1

There's quite a few ways to do what it seems like you are after:

Using a subquery to find max revenue for each comp:

SELECT Company, Total_Revenue
FROM myTable
  INNER JOIN 
    (
      SELECT Left(Company, 3) as leftcomp, 
        max(Total_Revenue) as maxTotalRevenue
      FROM mytable
      GROUP BY Left(Company, 3) 
    ) mt
    ON Left(myTable.Company, 3) = mt.leftcomp
    AND myTable.Total_Revenue = mt.maxTotalRevenue;

Window function that is later filtered by Where:

SELECT *
FROM
  (
    SELECT Company, 
      Total_Revenue,
      MAX(Total_Revenue) OVER (PARTITION BY Left(Company, 3)) as maxTotalRevenue
    FROM myTable
  ) mt
 WHERE Total_Revenue = maxTotalRevenue;

Correlated subquery in the WHERE clause:

 SELECT *
 FROM myTable mt1
 Where Total_Revenue =
   (
     SELECT max(total_revenue)
     FROM myTable
     WHERE Left(myTable.Company, 3) = Left(mt1.Company, 3)
   );

SQLFiddle here