CODEWITHSUNDEEP
mysqlsql
Grigor
Grigor

Reputation: 4049

SQL to select and order with condition

In my current code,

$sql = "SELECT *, SUM(news_points.points) as `total`
CASE `total` WHEN 0 THEN ORDER BY `news_id` ASC
CASE `total` WHEN NOT 0 THEN ORDER BY `total` DESC
FROM `news`,`news_points`";

I have two tables, news and news_points. What I want to do is select news based on news_points column named points. If a news has no points or is negative put it below the "good" news, which are the ones that have positive points or atleast are not negative.

Some schema that might help.

news table

news_id -- news_title
  1 -- New publication available.
  2 -- Check out this new car!
  3 -- Something else.

news_points table

point_id -- news_id -- point
  1 -- 1 -- 2
  2 -- 1 -- 5
  3 -- 1 -- -4
  4 -- 2 -- 5
  5 -- 3 -- -2

Based on the example above. The news with news_id=1 will have 3 points, news_id=2 will have 5 points and news_id=3 will have -2 points, therefore news with news_id 2 will be first, news with news_id 1 will be second and news_id will be last in the returned row. But if none of them have rating or only some, the rest just show in news_id descending.

How could I write this SQL to correctly get results?

Upvotes: 0

Views: 195

Answers (2)

gkalpak
gkalpak

Reputation: 48212

Your query should look like this: 

$sql = "SELECT news.*, SUM(news_points.points) as total
          FROM news left join news_points 
            ON news.news_id = news_points.news_id
          GROUP BY news.news_id
          ORDER BY total DESC, news.news_id ASC";

See, alsp, this short demo.

Upvotes: 3

Dinup Kandel
Dinup Kandel

Reputation: 2505

your question are little bit difficlult to understand. I tried my best check to see the result

 select news.news_id, sum(point) as sum, news.*  from 
news left join news_points on news.news_id= news_points.news_id
group by news.news_id order by sum desc

also see sql fiddle to your answer

see you example

Upvotes: 1

Related Questions