1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
| with t1 (v, c, x) as
(
select 0, 0, 'x1' union all
select 1, 0, 'x2' union all
select 3, 0, 'x3' union all
select 2, 0, 'x4' union all
select 2, 1, 'x5' union all
select 0, 1, 'x6' union all
select 2, 2, 'x7' union all
select 1, 2, 'x8' union all
select 0, 2, 'x9'
)
, t2 (c, d) as
(
select 0, 'min' union all
select 1, 'min' union all
select 2, 'max'
)
, t3 (c, v) as
(
select t2.c
, case t2.d when 'min' then MIN(t1.v) when 'max' then MAX(t1.v) end
from t1
inner join t2
on t2.c = t1.c
group by t2.c, t2.d
)
select t1.c, t1.x
from t3
inner join t1
on t1.c = t3.c
and t1.v = t3.v
order by t1.c asc;
c x
----------- ----
0 x1
1 x6
2 x7 |
Partager