CODEWITHSUNDEEP
mysql
Peter
Peter

Reputation: 630

Select Row with highest value between two tables while also selecting other columns in group by

Been stuck on figuring out the query for this.

Offers Database contains these columns: Customer_ID, Date, Trigger, Priority, PriorityOverRide.

I have the query that selects the largest value between Priority and PriorityOverride but I cant seem to figure out to also select the row where the value was taken from.

Here is my query at the moment:

SELECT
Customer_ID,
CASE WHEN offers.PriorityOverride > 0 THEN offers.PriorityOverride ELSE 
Priority END AS 'Priority'
FROM
(
SELECT
    Customer_ID,
    MAX(Priority) AS Priority,
    MAX(PriorityOverride) AS PriorityOverride
FROM
    offers
GROUP BY
    CUstomer_ID
) AS offers

The above query chooses the MAX value of Priority and PriorityOverride and selects the one that bigger.

Now Im having trouble with also selecting the Date and Trigger column where the Priority was selected from.

Edit: More information: PriorityOverride can be null or empty but if it is not empty or null, its value supersedes whatever Priority has.

Edit2: Here is my updated Query:

SELECT Customer_ID, Date, Trigger_1, Priority, PriorityOverride
  FROM Offers
  WHERE (Customer_ID, Priority) in (
    SELECT
        Customer_ID,
        CASE WHEN
            MAX(PriorityOverride) > 0
        THEN
            MAX(PriorityOverride)
        ELSE
            MAX(Priority)
        END AS 'Priority'
    FROM
        Offers
    GROUP BY
        Customer_ID
  ) 
  OR (Customer_ID, PriorityOverride) in (
    SELECT
        Customer_ID,
        CASE WHEN
            MAX(PriorityOverride) > 0
        THEN
            MAX(PriorityOverride)
        ELSE
            MAX(Priority)
        END AS 'Priority'
    FROM
        Offers
    GROUP BY
        Customer_ID
  )

However the above query gives me an error of " Errors: An expression of non-boolean type specified in a context where a condition is expected, near ','."

Upvotes: 0

Views: 54

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

You could use greatest for get the bigger between max values and filter for matching value on Customer_ID, Priority or Customer_ID, PriorityOverride

  select Customer_ID, Date, Trigger, Priority, PriorityOverRide
  from offers 
  where (Customer_ID, Priority) in (
    SELECT
        Customer_ID,
        max( case when Priority >  PriorityOverride
          THEN Priority
          ELSE  PriorityOverride
        end )
    FROM  offers
    GROUP BY  CUstomer_ID
  ) 
  or  (Customer_ID, PriorityOverride) in (
    SELECT
        Customer_ID,
        max(case when Priority >  PriorityOverride
          THEN Priority
          ELSE  PriorityOverride
        end )
    FROM  offers
    GROUP BY  CUstomer_ID
  )

Upvotes: 2

Related Questions