i have tables like this
table 1
x
|cl.1|
| -- |
| a |
| b |
| c |
table 2
|cl.1|cl.2|para|
|----|---| --- |
| a | 3 | t |
| a | 3 | f |
| b | 2 | t |
| a | 1 | b |
| c | 4 | t |
| b | 7 | d |
i want to get the max value for each element in table1 from table2
and the different parameter
so the expecited tabel should be like this
|cl.1|max|para|
|----|---| --- |
| a | 3 | t |
| a | 3 | f |
| c | 4 | t |
| b | 7 | d |
Advertisement
Answer
You can try to compute all the maximums:
with Maxes as (
select cl1,
max(cl2) as cl2
from Table2
group by cl1)
and then join
them with the original Table2
, e.g.
with Maxes as (
select cl1,
max(cl2) as cl2
from Table2
group by cl1)
select t.*
from Table2 t join
Maxes m on (t.cl1 = m.cl1 and t.cl2 = m.cl2)