Postgres group by query

Question

I am trying to use group by in a query in postgres. I cannot get it to work as I would like in order to group results as I need.

This is an extension to another stack question on recursion queries I just had answered. But now I need to be able to group the results on the root_id column of the final query. Here is the query before:

select cl.parent_comment_id, 
     cl.article_comment_id,
     cl.comment, 
     cl.article_id,
     cl.comment_depth
from comment_list cl
order by cl.root_id, cl.article_comment_id, cl.comment_depth;

Here is what I would like to do so that any records with the same parent_comment_id are kept together.

select cl.parent_comment_id, 
     cl.article_comment_id,
     cl.comment, 
     cl.article_id,
     cl.comment_depth
from comment_list cl
group by cl.parent_comment_id
order by cl.parent_comment_id, cl.article_comment_id, cl.comment_depth;

There can be many records with the same parent_comment_id returned, but distinct records for any given article_comment_id. i.e. each comment is unique (id, comment, title, etc.) but each parent comment can have many children. This has already been retrieved by the recursive query, now I am just trying to group them correctly.

Edit:

Take a look at http://www.sqlfiddle.com/#!12/77771/2 .What I would like is for article_comment_id=6 to follow immediately below article_comment_id=3 since id=3 is the parent. Then article_comment_id=4.

However I am thinking this needs to be done procedurally.

So I think this is a 'never mind' type of question unless someone knows how (which is why I am leaving it up). But I think I am going to try to solve this part procedurally.

RichardTheKiwi · Accepted Answer · 2012-11-09 21:43:16Z

4

For your recursive query, you can create a hierarchical path using this trick with 0-padded strings: SQL Fiddle

with recursive comment_list(article_comment_id, parent_comment_id, comment, article_id, comment_depth, comment_path) AS (
    select c.article_comment_id, 
           c.parent_comment_id, 
           c.comment, 
           c.article_id, 
           c.comment_depth,
           substr(CAST(1000000000+c.article_comment_id as varchar(1000)),2)
    from test_comment c
    where article_id = 100
      and parent_comment_id = 0

  union all

    select c.article_comment_id, 
           c.parent_comment_id, 
           c.comment, 
           c.article_id, 
           c.comment_depth,
           cl.comment_path || substr(CAST(1000000000+c.article_comment_id as varchar(1000)),2)
    from test_comment c
       join comment_list cl on c.parent_comment_id = cl.article_comment_id
)
select cl.article_comment_id,
     cl.comment_path, 
     cl.parent_comment_id,
     cl.comment, 
     cl.article_id,
     cl.comment_depth
from comment_list cl
order by cl.comment_path, cl.article_comment_id, cl.comment_depth;

Drop the GROUP BY. You want to "group" them for display, which is really "ORDER BY"

select cl.parent_comment_id, 
     cl.article_comment_id,
     cl.comment, 
     cl.article_id,
     cl.comment_depth
from comment_list cl
order by cl.parent_comment_id, cl.article_comment_id, cl.comment_depth;

You may or may not still need the cl.root_id in the order by, so it could be

order by cl.root_id, cl.parent_comment_id, cl.article_comment_id, cl.comment_depth;

edited Nov 9, 2012 at 21:43

answered Nov 9, 2012 at 20:27

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

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

3 Comments

Bill Rosmus Over a year ago

Take a look at my edit... I think I can't do do this in entirely in a sql statement. Order by doesn't solve the problem.

Bill Rosmus Over a year ago

That's hilarious. I like tricks like this. I was trying to think of something that would work all weekend. I thought about something like this but passed on it... but I didn't think of casting it to a string. Too rusty. :D

Bill Rosmus Over a year ago

Got it working with JPA as native query. Had to add a transient field for the path. Of course also flushed it out with all the extra fields I didn't muck up the question with. Thanks for the help.

Collectives™ on Stack Overflow

Postgres group by query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related