PostgreSQL query works locally but not on Heroku (same data)

Here is query:

ActiveRecord::StatementInvalid (PGError: ERROR: syntax error at or near "over" select *, rank() over (partition by thread_i... ^

     SELECT *
     FROM (                                                                  
             select *, rank() over (partition by thread_id order by created_at DESC)
             from posts
          开发者_运维技巧   where circle_id IN (134) OR (receiver_id=3)
           ) as dt
     WHERE rank = 1

EDIT: here is a detailed explanation of what I am trying to do: Rails 3 app with PostgreSQL - Getting the list of messages grouped by converstation

It turns out the Heroku shared DB is a PostgreSQL version 8.3 therefore no windows function so the question become how can I do this query in PostgreSql 8.3?

THANKS!

---

try

SELECT p.*
 FROM (                                                                  
         select x.thread_id, max(x.created_at) as maxdt
         from posts x
         where x.circle_id IN (134) OR x.receiver_id=3
         group by x.thread_id
       ) as dt
 INNER JOIN posts p ON p.thread_id = dt.thread_id and p.created_at = dt.maxdt 
 ORDER BY p.created_at DESC

EDIT - as per comment:

SELECT p.*
 FROM (                                                                  
         select x.thread_id, max(x.created_at) as maxdt, max (OID) maxo
         from posts x
         where x.circle_id IN (134) OR x.receiver_id=3
         group by x.thread_id
       ) as dt
 INNER JOIN posts p ON p.thread_id = dt.thread_id and p.created_at = dt.maxdt AND p.OID = dt.maxo
 ORDER BY p.created_at DESC

---

This should get you there:

 SELECT p.*
 FROM (                                                                  
         select thread_id, max(created_at) as maxdt
         from posts
         where circle_id IN (134) OR (receiver_id=3)
       ) as dt
 INNER JOIN posts p ON p.thread_id = dt.thread_id and p.created_at = dt.maxdt