0

In reference to the post "Retrieving the last record in each group", I adapted my query (using postgresql) to look for the last object of MATABLE on the zone MAZONE whose attribute created_at is the most recent:

SELECT 
  p1.id, 
  p2.id 
FROM MATABLE p1 
  LEFT JOIN MATABLE p2 
    ON (p1.zone_id = p2.zone_id AND p1.created_at < p2.created_at) 
WHERE 
  p2.id IS NULL 
  AND p1.zone_id = 'MAZONE';

The problem is that I get the following response:

id  | id 
----+----
(0 rows)

However, if I filter my table like this:

SELECT 
  p1.id, 
  p2.id 
FROM MATABLE p1 
  LEFT JOIN (SELECT * FROM MATABLE WHERE zone_id='MAZONE') p2 
    ON (p1.zone_id = p2.zone_id AND p1.created_at < p2.created_at) 
WHERE 
  p2.id IS NULL 
  AND p1.zone_id = 'MAZONE';

I get a result:

id    | id 
------+----
12716 |   
(1 row)

Any idea about this problem?

Thanks in advance!

Community
  • 1
  • 1
Baptiste Amato
  • 73
  • 1
  • 6
  • At first glance, the two queries look the same. Please, provide your schema definition and test data. – pumbo Dec 07 '16 at 10:58
  • MATABLE(id INT PRIMARY KEY, name VARCHAR, zone_id VARCHAR NOT NULL, created_at TIMESTAMP NOT NULL) I have a ten thousand rows in this table, maybe it has an impact on the query... – Baptiste Amato Dec 07 '16 at 15:01

0 Answers0