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.
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 ) ;
You can use two queries that group by
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
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 🙂