5

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.

3
  • We very much prefer exact table definitions that also show data types, primary key and unique constraints, NOT NULL constraints etc. What you get with \d tbl in psql (the default terminal). It is essential to know what is unique and what can be NULL ... Mar 14, 2014 at 2:18
  • +1 for your edits. And you had known the correct term (rank) but you didn't know that you knew :) Mar 14, 2014 at 18:41
  • Great first question. Very high quality. Color me impressed.
    – Zane
    Mar 14, 2014 at 18:53

1 Answer 1

5
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.