Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Question Unanswered: GROUP BY while maintaining original order?

    Consider the following example table and values:

    Code:
    test=# select * from tb_example;
     sequence_number | value1 | value2
    -----------------+--------+--------
                   1 | A      | foo
                   2 | A      | bar
                   3 | B      | baz
                   4 | B      | qux
                   5 | A      | quux
    (5 rows)
    And then the following query:


    Code:
    test=# select value1, array_agg(value2) from tb_example group by value1;
     value1 |   array_agg
    --------+----------------
     A      | {foo,bar,quux}
     B      | {baz,qux}
    (2 rows)

    I want a way to group the output similar to GROUP BY, such that non-adjacent rows with the same values for value1 are not grouped together. In other words, I want the output to look like this:

    Code:
    value1 |   array_agg
    --------+----------------
     A      | {foo,bar}
     B      | {baz,qux}
     A      | {quux}
    (3 rows)
    Is there any way for me to accomplish this? Thanks.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Try this one, at least with your sample data, it returns what you want:

    Code:
    select value1, array_agg(value2)
    from (
      select *,
             sum(group_flag) over (order by sequence_number) as group_nr
      from (
        select sequence_number, 
               value1, 
               value2,
               case when lag(value1) over (order by sequence_number) = value1 then null else 1 end as group_flag
        from tb_example
      ) t1
    ) t2
    group by value1, group_nr
    order by group_nr

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •