"Ranking" values in an ORDER BY clause?

Question

I have the following table:

                                     Table "public.employee_employee"
     Column      |         Type          |                           Modifiers                            
-----------------+-----------------------+----------------------------------------------------------------
 id              | integer               | not null default nextval('employee_employee_id_seq'::regclass)
 name            | text                  | not null
 slug            | character varying(50) | not null
 title           | text                  | not null
 base            | numeric(10,2)         | 
 gross           | numeric(10,2)         | 
 overtime        | numeric(10,2)         | 
 benefits        | numeric(10,2)         | 
 total           | numeric(10,2)         | 
 other           | numeric(10,2)         | 
 year            | smallint              | not null
 jurisdiction_id | integer               | not null
 notes           | text                  | 
Indexes:
    "employee_employee_pkey" PRIMARY KEY, btree (id)
    "employee_employee_jurisdiction_id" btree (jurisdiction_id)
    "employee_employee_slug" btree (slug)
    "employee_employee_slug_like" btree (slug varchar_pattern_ops)
    "employee_name_title_idx" gin (to_tsvector('english'::regconfig, (name || ' '::text) || title))
Check constraints:
    "employee_employee_year_check" CHECK (year >= 0)
Foreign-key constraints:
    "jurisdiction_id_refs_id_9e093e72" FOREIGN KEY (jurisdiction_id) REFERENCES jurisdiction_jurisdiction(id) DEFERRABLE INITIALLY DEFERRED

Here's a simplified sample of some mock data for the id, jurisdiction_id, year, and total columns:

1 | 1 | 2014 | 100.00
2 | 1 | 2012 | 105.00
3 | 1 | 2011 | 110.00
4 | 2 | 2013 | 115.00
5 | 2 | 2012 | 120.00
6 | 2 | 2011 | 125.00
7 | 3 | 2012 | 130.00
8 | 3 | 2011 | 135.00
9 | 4 | 2011 | 140.00

Or, presented another way, each unique jurisdiction_id has the following year values:

1: 2014, 2012, 2011
2: 2013, 2012, 2011
3: 2012, 2011
4: 2011

I'd like to "rank" these values the following way:

(1, 2014), (2, 2013), (3, 2012), (4, 2011) would be ranked first.
(1, 2012), (2, 2012), (3, 2011) would be ranked second.
(1, 2011), (2, 2011) would be ranked third.

Then, within each rank, the rows would be sorted by total DESC.

Here's the "SELECT (id, jurisdiction_id, year, total) ..." output I'm looking for (with line breaks between each rank):

9 | 4 | 2011 | 140.00
7 | 3 | 2012 | 130.00
4 | 2 | 2013 | 115.00
1 | 1 | 2014 | 100.00

8 | 3 | 2011 | 135.00
5 | 2 | 2012 | 120.00
2 | 1 | 2012 | 105.00

6 | 2 | 2011 | 125.00
3 | 1 | 2011 | 110.00

I have to think this is doable in PostgreSQL, but I don't even know the correct "term" for what I'm trying to do, so googling around has been a bit tough.

Answer 1

select 
rank() over (partition by jurisdiction_id order by year desc) AS rank,
*
from your_table
order by rank,total desc;

rank() over (...) is a window function, see: window functions in postgresql