StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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:</p> <pre><code>select * from yourTable order by case when "reply id" = 0 then id else "reply id" end, id </code></pre> <p><strong>ADDED:</strong> 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):</p> <pre><code>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; </code></pre> <p>You may need to adjust syntax for postgre. I tested this in SQL Server.</p> <p>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 <em>also</em> explain the basis for that order. Here's what I did:</p> <ol> <li><p>All messages in a thread (thread = a messages and its comments) should be grouped together.</p></li> <li><p>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.)</p></li> </ol> <p><strong><em>Note:</em></strong> 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 <em>before</em> 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.)</p> <p>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:</p> <pre><code>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; </code></pre> <p><strong>ADDED #2</strong>: 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):</p> <pre><code>where parentfeed.id = ( select coalesce(reply_id,id) from feed where id = ::thisOne ) </code></pre> <p>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</p> <pre><code>where parentfeed.id = ::thisOneThreadID </code></pre> <p>For the second solution, assuming you precompute again, try</p> <pre><code>where coalesce(id,reply_id) = ::thisOneThreadID </code></pre> <p>By the way, I suspect both of my solutions will merge threads that were last modified at the exact same time...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload