Helpful answer as the second post - sort order asc

Question

A forum has topics and in this topics are the posts. The sort order is from old to new. It is possible to rate each post with "helpful".

A default SQL selection looks like this:

SELECT * FROM `posts` WHERE `topic_id` = 5033 ORDER BY `post_id` ASC

The "helpful" field in the posts table has the name "post_helpful".

Is it possible to order the posts in this way:

First post - Contains the question
If a post with more then 3 "post_helpful" exists, display this post as the second post. But only the post with the highest score.
Normal post row without the second post id

I only want the post with the highest score on the second post position. But only if the post has more than 3 rates. If there is no post with more than 3 rates, keep the default order

Thank you

I don't follow your question. Please add sample data which explains what you are trying to do here. — Tim Biegeleisen, Oct 23 '19 at 14:08
A bit outside the scope of the question as it currently stands, but you might be able to benefit from [normalizing your database](https://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization). — esqew, Oct 23 '19 at 14:11
@tim Biegeleisen My question is, How is it possible to display the best answer as the second post. — Lars Vegas, Oct 23 '19 at 14:17
Again, please show us some data. As of right now, any answer I might give you would involve a _lot_ of speculation, and you shouldn't leave people guessing here. — Tim Biegeleisen, Oct 23 '19 at 14:18
@TimBiegeleisen sorry I would give you anything what you need but I dont know what exactly you are missing. Do you mean the normalizing of the database? — Lars Vegas, Oct 23 '19 at 14:34

Gordon Linoff · Accepted Answer · 2019-10-23T15:28:46.740

1

Yes. The first part is a little tricky. You can use multiple expressions in the ORDDER BY:

SELECT p.*
FROM posts p CROSS JOIN
     (SELECT MIN(p.post_id)  as min_post_id
      FROM posts p
      WHERE p.topic_id = 5033
     ) pp
WHERE p.topic_id = 5033
ORDER BY (p.post_id = pp.min_post_id) DESC,  -- lowest id first
         (case when p.post_helpful > 3 then p.post_helpful else 0 end) DESC,          -- helpful next
         p.post_id ASC;

EDIT:

To get the posts with the maximum helpful:

SELECT p.*
FROM posts p CROSS JOIN
     (SELECT MIN(p.post_id) as min_post_id,
             MAX(p.post_helpful) as max_post_helpful
      FROM posts p
      WHERE p.topic_id = 5033
     ) pp
WHERE p.topic_id. = 5033
ORDER BY (p.post_id = pp.min_post_id) DESC,  -- lowest id first
         (pp.max_post_helpful > 3 AND p.post_helpful = pp.max_post_helpful) DESC,          -- helpful next
         p.post_id ASC;

edited Oct 23 '19 at 15:28

answered Oct 23 '19 at 14:19

Gordon Linoff

1,242,037
58
646
786

thank you very much for getting into. This is almost correct. Your query display all helpful posts > 3 to the second post orderd by as but the second post needs only the post with the highest score. I hope you can edit it. – Lars Vegas Oct 23 '19 at 14:33
@LarsVegas . . . Why wouldn't you want all helpful posts before the rest? What if there are ties? I modified the answer so those posts that are most helpful are after the question. But there might be more than one. – Gordon Linoff Oct 23 '19 at 14:40
Thank you very much again for getting into. I dont want all helpful posts before the rest. I only want the post with the highest score on the second post position. But only if the post has more than 3 rates. If there is no post with more than 3 rates, keep the default order. thank you very much for your time. – Lars Vegas Oct 23 '19 at 14:47
please read my last comment. I tried your query and this query works very well but now all helpful posts comes in the second position with the order from high to low helpfull but like I said, I only want the post with the highest score on the second post position. It would be great if this is possible. – Lars Vegas Oct 23 '19 at 14:58
what should I say. Awesome...it works like a charm now :) thank you very much. I have marked the answer as fixed. There is only one lil error, after the WHERE p.topic_id is a dot. This dot has to be removed. – Lars Vegas Oct 23 '19 at 15:08

score 0 · Answer 2 · answered Oct 23 '19 at 14:21

0

I believe you want to have more complex sorting for the query Can you assign a value or weight by the count of rows that were flagged helpful This would then have a number to sort by

I.e. Query 2 counts the number of rows that show the post was helpful Query 1 is the main query ordered by the value/count from Query 2

answered Oct 23 '19 at 14:21

VAI Jason

534
4
14

1

Each post in a topic can be rated as helpfull. I try to show the highest helpful post as the second post. – Lars Vegas Oct 23 '19 at 14:36

Helpful answer as the second post - sort order asc

2 Answers2