getting next row in sql with a loop

Question

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

possible duplicate of How to compare the current row with next and previous row in PostgreSQL? — Godeke
– Godeke, Commented Oct 10, 2013 at 16:01

bvr · Accepted Answer · 2013-10-10 16:10:36Z

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

answered Oct 10, 2013 at 16:10

bvr

4,8361 gold badge23 silver badges24 bronze badges

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

1 Comment

serengeti12 Over a year ago

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

Collectives™ on Stack Overflow

getting next row in sql with a loop

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related