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

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 2

.... 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.)