sqlhana-sql-script

MIN&MAX value finding with condition

I have a table: enter image description here

I need to get below result: enter image description here

The Code should select row where "Number" value is MIN. If there is "Unique_1" where "Unique_2" != MIN, then take columns where "Number" value is MAX.

Current code:

select  T1.Unique_1, 
    T1.Unique_2,
    Case WHEN  T2.MIN_Number is not NULL then  T2.MIN_Number ELSE T3.MAX_Number end as Number from Table_1 as T1 LEFT JOIN ( 
SELECT  Unique_1,
        MIN(Number) as MIN_Number
From Table_1 WHERE Unique_2 = 'MIN' 
GROUP BY Unique_1) AS T2 ON  T1.Unique_1 = T2.Unique_1 LEFT JOIN( 
SELECT  Unique_1, 
        MAX(Number) as MAX_Number 
From Table_1 WHERE Unique_2 = 'MAX' 
GROUP BY Unique_1) AS T3 ON  T1.Unique_1 = T3.Unique_1

Hope I have clearly described the issue.

P.S. Thank you for your help and ideas.

Solution

  • You can do it using the conditional aggregation :

    SELECT unique_1, CASE WHEN minVal IS NOT NULL THEN 'MIN' ELSE 'MAX' END AS 'unique_2',
       COALESCE(minVal, maxVal) as number
FROM (
  SELECT unique_1, MIN(CASE WHEN unique_2 = 'MIN' THEN Number END) AS minVal,
                   MAX(CASE WHEN unique_2 = 'MAX' THEN Number END) AS maxVal
  FROM mytable
  group by unique_1
) AS s

    Result :

    unique_1    unique_2    number
222         MIN         1
333         MIN         1
444         MAX         3

    Demo tested on mysql here