Create custom numbering in Postgres

Question

I have a table that includes these columns:

track    track_id
-----------------
1        1
1        1
0        2
1        2
1        2
0        3
1        3
1        3
0        4
0        5
....

I have the column track and want to calculate track_id, which would be: Starting by 1, every time track is 0, track_id should be incremented by 1 (0 specifies the beginning of a new track).

I wish I could set a variable and then increment it every time the condition is true, but there does't seem to be a simple way to do that.

I would appreciate a query that can pull this. Thank you.

Gordon Linoff · Accepted Answer · 2017-08-08 23:37:20Z

1

First, you need a column that specifies the ordering. SQL tables represent unordered sets. Let me assume you have such a column, which I'll just call id.

Then, you can use a cumulative sum:

select t.*, 1 + sum( (track = 0)::int ) over (order by id) as track_id
from t;

If track only takes on the values 0 and 1, you can simplify this even more:

select t.*, 1 + sum(1 - track) over (order by id) as track_id
from t;

answered Aug 8, 2017 at 23:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Create custom numbering in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related