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
|
WITH ETABLISSEMENT(ID, NOM) AS
(
SELECT '1', 'AAA' union ALL
SELECT '2', 'BBB' union ALL
SELECT '3', 'CCC' union ALL
SELECT '4', 'DDD' union ALL
SELECT '5', 'EEE'
)
, PERIODE (ANNEE, TRIMESTRE, ETABLISSEMENT_FK, MONTANT) AS
(
SELECT '2009', '3', '1', 18 union ALL
SELECT '2009', '2', '1', 36 union ALL
SELECT '2009', '1', '1', 58 union ALL
SELECT '2008', '4', '1', 142 union ALL
SELECT '2008', '3', '1', 68 union ALL
SELECT '2008', '2', '1', 34 union ALL
SELECT '2009', '4', '2', 20 union ALL
SELECT '2009', '3', '2', 26
)
, SR (NOM, ANNEE, TRIMESTRE, MONTANT, rn) AS
(
SELECT et.NOM, pr.ANNEE, pr.TRIMESTRE, pr.MONTANT,
row_number() over(partition by et.ID order by pr.ANNEE desc, pr.TRIMESTRE desc)
FROM ETABLISSEMENT as et
INNER JOIN PERIODE as pr
ON pr.ETABLISSEMENT_FK = et.ID
WHERE et.NOM = 'AAA'
)
select NOM, ANNEE, TRIMESTRE, MONTANT
from SR
where rn = 1;
NOM ANNEE TRIMESTRE MONTANT
---- ----- --------- -----------
AAA 2009 3 18 |
Partager