CODEWITHSUNDEEP
sqlpostgresql
osh
osh

Reputation: 1261

Counting number of sorted subgroups inpostgresql

I would like to count the number of sorted subgroups in the table below :

id  nmb
1   11
2   12
3   13
4   22
5   23
6   31
7   32
8   33
9   11
10  12
11  13
12  12
13  13
14  21
15  22
16  11
17  12
18  13
19  14

And want to obtain something like this in postgresql 8.4 :

id  nmb local  
1   11  1  
2   12  1  
3   13  1  
4   22  1  
5   23  1  
6   31  1  
7   32  1  
8   33  1  
9   11  2  
10  12  2  
11  13  2  
12  12  3  
13  13  3  
14  21  3  
15  22  3  
16  11  4  
17  12  4  
18  13  4  
19  14  4

EDIT: the last few numbers on the 'local' column were wrong. Corrected!

Thank you.

Upvotes: 1

Views: 138

Answers (3)

dnoeth
dnoeth

Reputation: 60482

I though i finally understood what you want, consecutively growing values:

select id, nmb,
   sum(flag)
   over (order by id
         rows unbounded preceding) as local
from
 (
   select
      id, nmb,
      case
        when lag(nmb) 
             over (order by id) < nmb
          then 0 
        else 1
      end as flag
   from t
 ) as dt
order by id

But group 4 doesn't fit

Edit: Now they fit :-)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270583

It appears that you are trying to enumerate the groups, where a group starts with a lower nmb value than the previous row ("previous" defined by the id order).

The idea is to identify the start of a group by using lag(). Then take the cumulative sum to get the group identifier that you want:

select id, nmb, sum(StartFlag) over (order by id) as local
from (select id, nmb,
             lag(nmb) over (order by id) as lastnmb,
             (case when lag(nmb) over (order by id) < nmb then 0
                   else 1
              end) as StartFlag
      from t
     ) t
order by id;

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125444

SQL Fiddle

select
    id, nmb,
    row_number() over(partition by nmb order by id)
from t
order by id

Upvotes: 1

Related Questions