Comparing different rows in PostgreSQL for each Id

Question

Few columns in my table looks like

Id   Code   date        latest
1    T     2014-10-04    0
2    B     2014-10-19    0
2    B     2014-10-26    0
1    S     2014-10-05    0
1    T     2014-10-06    0
1    T     2014-10-08    1
2    P     2014-10-27    1

I am tracking all changes made by each ID. if there is any change, I insert new row and update the latest value column.

What I want is for each Id, I should be able to find last code where latest is 0. Also, that code should not be equal to existing code(latest = 1) So for id = 1, answer cannot be

 Id   Code
 1    T

as for id = 1 T is existing code (latest = 1).
So ideally my output should look like:

Id    Code
 1     S
 2     B

I think I can get the latest value for code for each id where latest = 0.

But how do I make sure that it should not be equal to existing code value (latest = 1)

score 0 · Answer 1 · answered Oct 31 '14 at 02:30

0

I think the following does what you want:

select t.*
from (select distinct on (code) id, code
      from table t
      where latest = 0
      order by code, date desc
     ) t
where not exists (select 1 from table t2 where t2.id = t.id and t2.code = t.code and t2.latest = 1);

answered Oct 31 '14 at 02:30

Gordon Linoff

1,242,037
58
646
786

I am sorry, but this does not work when we have multiple latest = 0 for an Id, with different code. I have 6-7 different types of code – Neil Oct 31 '14 at 03:23
It returns multiple rows in that case – Neil Oct 31 '14 at 03:29
@Neil . . . The phrasing of the question (and sample data) suggest that you only want to return one row per code. Are you saying you want more than one row per code in the output? – Gordon Linoff Oct 31 '14 at 10:39

score 0 · Answer 2 · answered Oct 31 '14 at 02:59

I believe you should have a data for the current version and you should create another table where you would store previous revisions, having foreign key to the Id. Your Id does not fulfill the general expectations for a column with such a name. So, ideally, you would:

create a table Revisions(Id, myTableId, core, date, revision), where Id would be auto_increment primary key and myTableId would point to the Id of the records (1 and 2 in the example)
migrate the elements into revision: insert into Revisions(myTableId, core, date, revision) select Id, core, date latest from MyTable where latest = 0
update the migrated records: update Revisions r1 set r1.revision = (select count(*) from revisions r2 where r2.date < r1.date)
remove the old data from your new table: delete from MyTable where latest = 0
drop your latest column from MyTable

From here, you will be always able to select the penultimate version, or second to last and so on, without problems. Note, that my code suggestions might be of wrong syntax in postgreSQL, as I have never used it, but the idea should work there as well.

Thanks for the suggestion. Id is not the primary key here (and not the actual column name). I have separate primary key in the table which i have not represented here. — Neil, Oct 31 '14 at 03:01

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

Works in Postgres:

SELECT DISTINCT ON (t0.id)
       t0.id, t0.code
FROM   tbl t0
LEFT   JOIN tbl t1 ON t1.code = t0.code
                  AND t1.id = t0.id
                  AND t1.latest = 1
WHERE  t0.latest = 0
AND    t1.code IS NULL
ORDER  BY t0.id, t0.date DESC;

I use the combination of a LEFT JOIN / IS NULL to remove siblings of rows with latest = 1. There are various ways to do this:

Select rows which are not present in other table

Details for DISTINCT ON:

Select first row in each GROUP BY group?

Version with CTE and 2x LEFT JOIN

Since Redshift does not seem to support DISTINCT ON:

WITH cte AS (
   SELECT t0.*
   FROM   tbl t0
   LEFT   JOIN tbl t1 ON t1.code = t0.code
                     AND t1.id = t0.id
                     AND t1.latest = 1
   WHERE  t0.latest = 0
   AND    t1.id IS NULL
   )
SELECT c0.id, c0.code
FROM   cte c0
LEFT   JOIN cte c1 ON c1.id = c0.id
                  AND c1.date > c0.date
WHERE  c1.id IS NULL
ORDER  BY c0.id;

SQL Fiddle showing both.

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 31 '14 at 08:39

Erwin Brandstetter

605,456
145
1,078
1,228

It gives me error distinct on is not supported. May be because I am using Redshift which is based on PostgreSQl 8.0.2 – Neil Oct 31 '14 at 09:00
@Neil: A quick check indicates Redshift supports CTEs. Added a solution. – Erwin Brandstetter Oct 31 '14 at 09:26
Checking above code. I guess works in most of the cases. Though I see few rows are missing from output. Need to debug why are they missing. – Neil Oct 31 '14 at 10:05
@Neil: It shouldn't work "most of the time", but all the time. – Erwin Brandstetter Oct 31 '14 at 10:52
Not sure, but could this be issue? https://forums.aws.amazon.com/thread.jspa?messageID=561464 – Neil Nov 03 '14 at 03:47
Apologies @Erwin It worked. I made a mistake while re writing the query to suit my needs. Thanks a lot for your help. – Neil Nov 03 '14 at 05:27

Comparing different rows in PostgreSQL for each Id

3 Answers3

Version with CTE and 2x LEFT JOIN