How to select max and min of 1 column based on other 2 columns?

All we need is an easy explanation of the problem, so here it is.

I’m trying to get the max and min date values in column C based on the (column A, column B) unique pair combinations.

Suppose I have a table like:

``````column_A column_B column_C
A        1        2019-08-11
A        1        2018-11-12
A        1        2017-11-12
A        11       2020-03-03
A        11       2021-01-10
A        11       2021-02-02
B        2        2020-11-11
B        2        2020-12-12
``````

The output I want to get is:

``````column_A column_B column_C
A        1        2019-08-11
A        1        2017-11-12
A        11       2020-03-03
A        11       2021-02-02
B        2        2020-11-11
B        2        2020-12-12
``````

My attempt query has been taking 20 mins to run with no output yet (just tried to get max date from column C for now):

``````SELECT column_A, column_B, column_C FROM table_name
WHERE column_C IN (
SELECT MAX(column_C) FROM table_name
GROUP BY column_A, column_B
)
``````

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Just use two conditions:

``````select t.*
from t
where column_c = (select max(t2.colc)
from table_name t2
where t2.column_A = t.column_A and t2.column_B = t.column_B
) or
column_c = (select min(t2.colc)
from table_name t2
where t2.column_A = t.column_A and t2.column_B = t.column_B
) ;

``````

Method 2

You can use two queries that group by `column_a` and `column_b`, one with the max and one with the min of `column_c`, and then `union all` them:

``````SELECT   column_a, column_b, MAX(column_c)
FROM     table_name
GROUP BY column_a, column_b
UNION ALL
SELECT   column_a, column_b, MIN(column_c)
FROM     table_name
GROUP BY column_a, column_b
``````

Method 3

you can create a temporary view and GROUP BY the desired columns

``````select column_a,column_b,max(column_c) as max_column_c
from (select column_a,column_b,column_c,to_char(column,'YYYY') as year
from table_t) aa
group by column_a,column_b,year
``````

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂