Pick Values from Grouped Rows in Postgres

Question

I have a table in Postgres (9.2) that looks like this:

id | code | year
 1 |  A   | 2014
 1 |  A   | 2015
 1 |  A   | 2016
 2 |  A   | 2014
 2 |  A   | 2015
 2 |  B   | 2016

I'm trying to combine rows based on the id field. I can do it with a self-join, but I'm trying to avoid scanning the table twice if possible (potentially millions of rows). I want to do something like this:

SELECT CASE year WHEN 2016 THEN code ELSE '' END AS code,
       CASE year WHEN 2015 THEN code ELSE '' END AS prev_code
FROM tbl
GROUP BY id
HAVING year = 2015 OR year = 2016

Ideally the output would look like this:

code | prev_code
  A  |    A         (from id=1)
  B  |    A         (from id=2)

When I run the query I've proposed, it tells me that year needs to be in the group by or an aggregate function. There are examples of queries like this on the internets, but they don't seem to be for Postgres. Any idea how to do this in Postgres 9.2?

Since you are not performing any aggregations why do even need GROUP BY ? — PM 77-1
– PM 77-1, Commented Apr 19, 2014 at 1:14
While I'm not using the id value in the return, I want to reduce the rows from two per ID to one per ID and being matched on the ID is required. — Nik
– Nik, Commented Apr 19, 2014 at 1:16

Thomas · Accepted Answer · 2014-04-19 01:22:29Z

1

You can probably use the Lag window function for this:

Select Z.Code, Z.PrevCode
From (
     Select Id, Code, Year
        , lag( Code, 1 ) Over ( Partition By Id Order By Year ) As PrevCode
     From tbl
     Where Year In(2016,2015)
     ) As Z
Where Z.Year = 2016

SQL Fiddle version

answered Apr 19, 2014 at 1:22

Thomas

64.8k12 gold badges98 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nik Over a year ago

Can I do lag once and get multiple values? e.g. lag(col_1, col_2, col_3, 1) OVER...

Thomas Over a year ago

@Nik - No, I believe you would need to use lag on each column you wished to returned. I.e., lag( col1...), lag( col2...) etc.

PM 77-1 · Accepted Answer · 2014-04-19 01:28:39Z

1

You can slightly modify your query as follows:

SELECT MAX(CASE year WHEN 2016 THEN code ELSE '' END) AS code,
       MAX(CASE year WHEN 2015 THEN code ELSE '' END) AS prev_code
FROM Table1 tbl
WHERE year IN(2015, 2016)
GROUP BY id;

SQL Fiddle

answered Apr 19, 2014 at 1:28

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

Collectives™ on Stack Overflow

Pick Values from Grouped Rows in Postgres

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related