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
| WITH MonResultat (FAM, SFAM, Nb, G1, G2) AS
(
select 'A', 'A', 5, 0, 0 union all
select 'A', 'B', 9, 0, 0 union all
select 'A', 'C', 3, 0, 0 union all
select 'A', '' , 17, 0, 1 union all
select 'B', 'A', 2, 0, 0 union all
select 'B', 'B', 5, 0, 0 union all
select 'B', 'C', 16, 0, 0 union all
select 'B', '' , 23, 0, 1 union all
select '' , '' , 40, 1, 1
)
select *
from MonResultat
order by max(case when g2 = 1 and g1 = 0 then Nb end) over(partition by fam) desc,
case when g2 = 1 and g1 = 0 then 0 else 1 end desc,
Nb desc;
FAM SFAM Nb G1 G2
---- ---- ----------- ----------- -----------
B C 16 0 0
B B 5 0 0
B A 2 0 0
B 23 0 1
A B 9 0 0
A A 5 0 0
A C 3 0 0
A 17 0 1
40 1 1 |
Partager