MIN&MAX value finding with condition

Question

I have a table:

I need to get below result:

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.

DO NOT SHARE IMAGE OF DATA OR ERRORS https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors — TimLer, May 19 '23 at 08:44

SelVazi · Accepted Answer · 2023-05-19T08:58:57.677

3

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

edited May 19 '23 at 08:58

answered May 19 '23 at 08:53

SelVazi

10,028
2
13
29

1

Thanks. I like this answer. some new function for me as a new guy in SQL :) – Petras May 19 '23 at 10:17

Jérôme Radix · Answer 2 · 2023-05-19T09:04:36.337

Here is the solution (Here is a demo on mariadb 10.9):

select unique_1, unique_2, min(number) as number
from table_1
where unique_2 = 'MIN'
group by unique_1, unique_2
union all
select unique_1, unique_2, max(number) as number
from table_1 ta
where unique_2 = 'MAX'
and not exists (select null
                  from table_1 tb
                  where tb.unique_1 = ta.unique_1
                  and tb.unique_2 = 'MIN')
group by unique_1, unique_2;

 unique_1 | unique_2 | number 
----------+----------+--------
      222 | MIN      |      1 
      333 | MIN      |      1 
      444 | MAX      |      3

MIN&MAX value finding with condition

2 Answers2