complex sql order by

Question

id    |    message    |    reply id    |    date  

1     |  my new app.. |     0          | 10 / 10 / 2009 (latest message on top, follow by replies)
5     | love ur app.. |     1          | 11 / 10 / 2009 (this should show under the main message)
6     | another comm  |     1          | 12 / 10 / 2009
2     | application 2 |     0          | 09 / 10 / 2009
3     | reply of 2    |     2          | 11 / 10 / 2009

I want to show latest comments and its replies after the main comments. Obviously the replies will have latest date, so I can't sort it by date, because replies will be on top of main. I'm not sure how to do this properly with one query. Any idea's please.

database dump: http://pastie.org/576963

Answer 1

Here is a link on representing hierarchical data in a relational database. http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

The gist being, you need to join to the table for as many levels deep you want to go. The example data provided was just two levels deep.

SELECT
f1.id AS `parent_id`, 
f1.text AS `parent_text`, 
f1.created_date AS `parent_created_date`, 
f2.*
FROM 
feed AS `f1`
LEFT JOIN feed `f2` ON (f1.id = f2.reply_id)
WHERE f1.reply_id = 0
ORDER BY f1.created_date DESC, f2.created_date DESC
;

The parent article information will be in the f1.* columns and the child information (replys) will be in the f2.* columns.

Answer 2

I'll guess that "reply id" is 0 for articles and is the article number for comments. If that's your design, this should work:

select * from yourTable
order by
  case when "reply id" = 0 then id else "reply id" end, id

ADDED: Thanks for the additional information in your comment. To put the results in the order you want is not so easy, because the first ordering key is the created_date of the thread-starter post. This is not in the data row, so you need a join. Here is my best guess based on the additional information (which is still not complete enough to keep me from guessing):

select
  f.id, f.user_id, f.type, f.reply_id, f.text, f.url, f.created_date,
  coalesce(parentfeed.created_date,f.created_date) as thread_date
from feed as f left outer join feed as parentfeed
on f.reply_id = parentfeed.id
order by
  thread_date desc,
  case when f.reply_id = 0 then 0 else 1 end,
  created_date desc, id;

You may need to adjust syntax for postgre. I tested this in SQL Server.

If this still doesn't do what you want, please be specific about how you want the data back. Preferably, tell me the "id" order I should see for the data in your dump file, and also explain the basis for that order. Here's what I did:

1. All messages in a thread (thread = a messages and its comments) should be grouped together.

2. Within a thread, put the message on top, followed by its comments in reverse chronological order. The thread with the most recent created/_date should be first, then the thread with the second most recent created_date, and so on. (Your sample data had many comments with the same created_date, so I used "id" as a secondary order key for the comments within a thread.)

Note: Your dump indicates that created_date is updated to CURRENT_TIMESTAMP if a post is modified. If this is a live message board, be aware that this might cause comments to be dated before the parent message, and it means a thread will stay on top if it is frequently modified (even with no actual change to its text). (That's not relevant to my solution, but I thought it was worth noting.)

Because a join is required, this query will now be much slower. My suggestion: maintain two date columns, "thread_last_modified" and "item_last_modified". You will have to cascade updates from thread-starters to comments, but I think it's worth it if there are not lots of updates, because the query can be much simpler. I haven't tested this because it requires several changes to your design:

select
  id, user_id, type, reply_id, text, url, thread_last_modified, item_last_modified
from feed
order by
  thread_last_modified desc,
  case when f.reply_id = 0 then 0 else 1 end,
  item_last_modified desc, id;

ADDED #2: If you want only the thread containing the comment with id ::thisOne, I think you can add this line between the ON and ORDER BY clauses (for my first added solution, the join):

where parentfeed.id = (
  select coalesce(reply_id,id)
  from feed
  where id = ::thisOne
)

In theory, this lookup should be evaluated just once for the query, but if it's not in practice, you could precompute it as ::thisOneThreadID and add

where parentfeed.id = ::thisOneThreadID

For the second solution, assuming you precompute again, try

where coalesce(id,reply_id) = ::thisOneThreadID

By the way, I suspect both of my solutions will merge threads that were last modified at the exact same time...

Answer 3

.... order by (some predicate which is true for "main comments" but not followups) desc, date desc

Since you don't make it clear what distinguishes a "main comment", that's all I can do for you. If, as in your example, all main comments had a reply_id of zero, this might be:

order by case reply_id = 0 then 1 else 0 end desc, date desc

Note that using the case statement rather than just ordering by reply_id is necessary, because you want the first expression to have the same value (namely, zero) for all non-main messages, so that they are sorted exclusively by the second expression, date.

(Oh, if I understand your schema, reply_id ought really be named in_repky_to_id.)

Answer 4

i'm not sure of your requirements, so my response is an abstraction... the max - may need to be a min (again, i'm not clear on your exact requirements)

select
    y.column1
    , y.column2
    , y.column3
    , y.datecolumn
from
(select 
    column1
    , max(datecolumn) as rank_date
 from tableA
 group by column1) as x
inner join tableA y on x.id = y.id
order by x.rank_date, y...., y.... desc, y... asc, etc...

if you are using sql server, there is a function called over row_number that you could use as well.