How do I increment a value within an SQL select statement based on values within the query

Question

If I have a table defined like this in a postgres 12.4 database

id, name, age, enters, exits

Within that table there can be mutliple entries with the same name.

I want to create a select statement that introduces a group_id column to the results. The group_id will increment based on the value of the exits column. If it contains null then the group_id needs to increment and subsequent rows will belong to the new group_id until the next exits=null is encountered.

For example if the info table contains:

id, name, age, enters, exits
1, orange, 10, null, 8
2, orange, 8, 3, 5
3, orange, 4, 9, null
4, orange, 11, null, 5
5, orange, 3, 3, null
6, lemon, 9, 1, 2

Then a select * type query would return this:

id, group_id, name, age, enters, exits
1, 1, orange, 10, null, 8
2, 1, orange, 8, 3, 5
3, 1, orange, 4, 9, null
4, 2, orange, 11, null, 5
5, 2, orange, 3, 3, null
6, 3, lemon, 9, 1, 2

I am relatively new to SQL and after lots of searches and attempts I haven't made any progress. Most of the examples out there are much more complicated than this and I don't understand enough to deconstruct them into something that works.

Any help or pointers appreciated.

You are trying to say, select * from my table order by group_id ? — NaturalCoder
– NaturalCoder, Commented Dec 2, 2020 at 21:06
@NaturalCoder sort of.. but the group_id doesnt exist in the original data. It needs to be conditionally created as part of the select statement — The Welsh Dragon
– The Welsh Dragon, Commented Dec 2, 2020 at 21:07
May be their is a solution, i dont have right now, but you can select all data and do this stuff on front end, while displaying it — NaturalCoder
– NaturalCoder, Commented Dec 2, 2020 at 21:10

GMB · Accepted Answer · 2020-12-02 21:09:56Z

1

You can do this with window functions, by counting how many null values appear in prior rows:

select t.*,
    1 + count(*) filter(where exits is null) over(
        order by id
        rows between unbounded preceding and 1 preceding
    ) as group_id
from mytable t

Demo on DB Fiddle:

id | name   | age | enters | exits | group_id
-: | :----- | --: | -----: | ----: | -------:
 1 | orange |  10 |   null |     8 |        1
 2 | orange |   8 |      3 |     5 |        1
 3 | orange |   4 |      9 |  null |        1
 4 | orange |  11 |   null |     5 |        2
 5 | orange |   3 |      3 |  null |        2
 6 | lemon  |   9 |      1 |     2 |        3

Alterntively:

select t.*,
    1 - (exits is null)::int + count(*) filter(where exits is null) over(order by id) as group_id
from mytable t

answered Dec 2, 2020 at 21:09

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

Daniel Gimenez Over a year ago

You could avoid using the filter by doing the following: 1 + COALESCE(SUM(CASE WHEN exits IS NULL THEN 1 ELSE 0 END .... However it's cool that Postgres has that at all - seems super useful. I wish other RDBSs had it.

The Welsh Dragon Over a year ago

@GMB quite a difference in performance between those two. The first option seems to perform better. Thank you, it does the trick. Is there a way to compare columns in the current row and the next row as part of this query? For example, if I only wanted to increment the group_id if exits=null AND the age of this row is not equal to the age of the next row?

Collectives™ on Stack Overflow

How do I increment a value within an SQL select statement based on values within the query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related