Translate MySQL to Postgres

Question

I am looking for help converting my MySQL query to work in PostgreSQL. This is a query on the model of RoR application. Any help is appreciated.

SELECT * FROM( 
   SELECT @row := @row + 1 AS rownum, id, device_id, name, quarterly 
   FROM (SELECT @row :=0) r, recurrent_tests 
   WHERE device_id = "+self.id.to_s+" AND quarterly = 1
   ORDER BY name ASC
) ranked 
WHERE (rownum-1) % 4 = "+(i-1).to_s)

Related: stackoverflow.com/questions/3959692/rownum-in-postgresql — leonbloy
– leonbloy, Commented Apr 15, 2013 at 19:41

user330315 · Accepted Answer · 2013-04-15 19:39:45Z

2

select *
from (
   select row_number() over (order by name asc) as rownum,
          id, 
          device_id, 
          name,
          quarterly
   from recurrent_tests
) t
where rownum - 1 % 4 = ...

For more details on window functions (the over (...) clause) please see the manual:
http://www.postgresql.org/docs/current/static/tutorial-window.html

answered Apr 15, 2013 at 19:39

user330315

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

Collectives™ on Stack Overflow

Translate MySQL to Postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related