#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    9
    Rep Power
    0

    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
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    I suspect not, at least not with a regular query. You would probably need to use a stored procedure.

    Although what would be best would be to add another column that you can use to logically group them.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    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
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    9
    Rep Power
    0

    Thumbs up


    Ingenious solution, shammat, but I decided to add another column for grouping, as E-Oreo suggested. Thanks folks.

IMN logo majestic logo threadwatch logo seochat tools logo