0

I might not have the best title for my question but this is what I mean. I have this result which results after a query with a couple of joins.

 id  | owner_id     |   name    |        order | count 
-----+--------------+-----------+--------------+-------
 274 |        25041 | first     |            1 |     0
 269 |        25041 | second    |            2 |     2
 275 |        25041 | third     |            3 |     0
 276 |        25041 | fourth    |            4 |     0
 273 |        25041 | fifth     |            5 |     1
 277 |        25041 | sixth     |            6 |     0

and I need a query that use is to add a column with the next name depending using the order column above. It should loop that it should say after the sixth follows the first. 

 id  | owner_id     |   name    |        order | count |   next    
-----+--------------+-----------+--------------+-------+-----------
 274 |        25041 | first     |            1 |     0 | second
 269 |        25041 | second    |            2 |     2 | third
 275 |        25041 | third     |            3 |     0 | fourth
 276 |        25041 | fourth    |            4 |     0 | fifth
 273 |        25041 | fifth     |            5 |     1 | sixth
 277 |        25041 | sixth     |            6 |     0 | first
serengeti12
  • 5,205
  • 4
  • 23
  • 27
  • possible duplicate of [How to compare the current row with next and previous row in PostgreSQL?](http://stackoverflow.com/questions/7974866/how-to-compare-the-current-row-with-next-and-previous-row-in-postgresql) – Godeke Oct 10 '13 at 16:01

1 Answers1

1

Try this

select t1.*
,CASE WHEN t2.name IS NULL THEN 'first' ELSE t2.name END as next 
from  Table1 as t1
LEFT join Table1 as t2 on t1.order = t2.order-1

SQL FIDDLE DEMO

bvr
  • 4,786
  • 1
  • 20
  • 24
  • Thanks!.. I doesn't work for me yet thought because 'first' here could be any text and Table1 actually a result of another query. so in my case it ... from (select ... ) as t1 – serengeti12 Oct 10 '13 at 16:30