Order By And Group By Items And Their Comments

[jimmyoneshot]

My situation is strange but basically I need to return a list of items and their comments in 1 query. The problem is this, I need to order the items from oldest to newest (bottom as oldest to top as newest) with all of the comments for that item displayed beneath each item with the oldest comment on top and the newest comment on the bottom. Very similar to Facebook. Like this:-

 

item 3

  comment1

  comment2

  comment3

 

item2

  comment1

  comment2

 

item1

  comment1

  comment2

  comment3

 

I've managed to get this data retrieved 1 query but it is the order by/group by methods which I need to employ that I am struggling to figure out. Here is what I've got so far:-

 

SELECT

@item:='item' AS row_type,
items.id AS row_item_id,
items.id AS id
items.body AS body
items.date AS date

UNION ALL

SELECT

@comment:='comment' AS row_type,
items.id AS row_item_id,
item_comments.id AS id
item_comments.body AS body
item_comments.date AS date

LEFT JOIN items
ON (item_comments.id2 = items.id)

GROUP BY row_item_id ORDER BY id desc

 

The above query is a very simplified version of my full query but basically this groups everything correctly I THINK but it is getting the desired ordering of items with newest on top and comments with newest on bottom which I can't figure out. Can anybody please help? Thanks in advance

[Muddy_Funster]

...
GROUP BY row_item_id ORDER BY id DESC, row_item_id ASC

 

Is that what you're looking for?

[jimmyoneshot]

Thanks muddy. I ended up simply putting parentheses around each query and ordering the item query by date desc and the comments by date desc