Re: Multiple use of RANK analytic in a single query
From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Mon, 14 Nov 2011 16:29:54 +1100
Message-ID: <MPG.292b5259d48e2bf97f_at_news.x-privat.org>
geoff.muldoon_at_trap.gmail.com says...
RANK () OVER (PARTITION BY LOAD_YEAR, ORG_UNIT_CODE
FROM STAFF_LOAD
GROUP BY LOAD_YEAR, ORG_UNIT_CODE, CLASSIF_CODE; This gives me:
Date: Mon, 14 Nov 2011 16:29:54 +1100
Message-ID: <MPG.292b5259d48e2bf97f_at_news.x-privat.org>
geoff.muldoon_at_trap.gmail.com says...
Self-follow-up with what I've tried so far, this time with real-ish code and data:
SELECT
LOAD_YEAR,
ORG_UNIT_CODE,
RANK () OVER (PARTITION BY LOAD_YEAR, CLASSIF_CODE
ORDER BY SUM (FTE_DAYS) DESC) ORG_RANK,
CLASSIF_CODE,
RANK () OVER (PARTITION BY LOAD_YEAR, ORG_UNIT_CODE
ORDER BY SUM (FTE_DAYS) DESC) CLASSIF_RANK,
SUM (FTE_DAYS) FTE
FROM STAFF_LOAD
GROUP BY LOAD_YEAR, ORG_UNIT_CODE, CLASSIF_CODE; This gives me:
YEAR ORG_UNIT_CODE ORG_RANK CLASSIF_CODE CLASSIF_RANK FTE
2010 A46 1 HEW3 1 59
2010 A42 2 HEW3 1 13
2010 A42 1 HEW4 1 13
2010 A46 2 HEW4 2 12
What I want is:
YEAR ORG_UNIT_CODE ORG_RANK CLASSIF_CODE CLASSIF_RANK FTE
2010 A46 1 HEW3 1 59
2010 A42 2 HEW3 1 13
2010 A42 2 HEW4 2 13
2010 A46 1 HEW4 2 12
Geoff M
Received on Sun Nov 13 2011 - 23:29:54 CST