How to get next value if exists in postgresql

Question

I have a table A as foolows:

id name      nextid    orderset
19  done               4
21  init       27      1
24  sent-req   19      3
27  offer-req  24      2

the orderset column gives me the order of the status: init -> offer-req -> sent-req -> done

I need to write a query that for a given id, it gives the id of the next status.

That should be easy:

select id
from A
where orderset= (select orderset+1 from A where id=Any_Given_ID)

That works in all cases except the last id... as there is no next to id=19, In the case of the last one I want the query to return 0.

I thought of doing a check with Max(orderset) and select order+1 from A where id=Any_Given_ID but I just can't seem to make it work.

Is it doable in a query or do I have to write a function for this operation?

Gordon Linoff · Accepted Answer · 2015-08-16 13:32:27Z

2

Assuming the orders are consecutive, just use lead():

select id
from (select coalesce(lead(id) over (order by orderset), 0) as id
      from A
     ) a
where id = Any_Given_ID;

You can also use your method with aggregation. That guarantees that one row is returned:

select coalesce(max(id), 0)
from A
where orderset = (select orderset + 1 from A where id = Any_Given_ID);

edited Aug 16, 2015 at 13:32

answered Aug 13, 2015 at 13:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

John Over a year ago

I'm confused... The reason I didn't though of using coalesce(something, 0), is because if first argument exists it always return him. So why would it return 0? max(id) exists.

Gordon Linoff Over a year ago

@John . . . If the where clause has no matches, then the max() returns NULL.

John Over a year ago

thx. Can you also explain the lead example? The postgresql manual says: "returns value evaluated at the row that is offset rows after the current row within the partition" it's not very clear.

Gordon Linoff Over a year ago

@John . . . There is no partition, so the entire data set is used. It gets the next value based on orderset.

Collectives™ on Stack Overflow

How to get next value if exists in postgresql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related