select data from another table with max date in hive

user_9323888 0
Viewed 365

I have one table t1 like this

A     B
1    2020-05-01
1    2020-05-04
1    2020-05-05
1    2020-05-06
2    2020-04-10

and another table t2

A     C
1    2020-04-30
5    2020-04-08

and I need out like this:

A     B             c
1    2020-05-01    2020-04-30
1    2020-05-04    2020-04-30
1    2020-05-05    2020-04-30
1    2020-05-06    2020-04-30
2    2020-04-10    2020-04-08

As you can see i am getting last max date as c from table t2 which less than B here 2020-04-30 is the max date less than 2020-05-01,04,05 and 06, and for 2020-04-10 the date is 2020-04-08.

I am trying it like this but getting wrong answer:

select t1.*,t2.C, max(C) over (partition by t2.A ) from t1 inner join t2 on t1.A=t2.A and t2.C<t1.B
hive sql
2 Answers

You could try this approach. I use CTE(Common Table Expresion) and query the CTE with MAX and GROUP BY

WITH t AS(
SELECT t1.a, t1.b, t2.c
FROM t1, t2
WHERE t1.b > t2.c)
SELECT a, b, MAX(c) AS c
FROM t
GROUP BY a,b;

expected output

+----+-------------+-------------+--+
| a  |      b      |      c      |
+----+-------------+-------------+--+
| 1  | 2020-05-01  | 2020-04-30  |
| 1  | 2020-05-04  | 2020-04-30  |
| 1  | 2020-05-05  | 2020-04-30  |
| 1  | 2020-05-06  | 2020-04-30  |
| 2  | 2020-04-10  | 2020-04-08  |
+----+-------------+-------------+--+
user_85714980

You can try this:

Select t1.A,t1.B,MAX(t2.B) from t1 t1 join t2 t2 on t1.A=t2.A group by t1.A,t1.B;
user_124016980
Related