CODEWITHSUNDEEP
sqlpostgresql
Nik
Nik

Reputation: 7273

Pick Values from Grouped Rows in Postgres

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?

Upvotes: 0

Views: 36

Answers (2)

PM 77-1
PM 77-1

Reputation: 13334

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

Upvotes: 1

Thomas
Thomas

Reputation: 64635

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

Upvotes: 1

Related Questions