开发者

Ordering group by in MySQL

开发者 https://www.devze.com 2023-03-27 21:22 出处:网络
Right to the questioin. I have articles that are being grouped by country. I\'m counting the votes on every article from another table using a nested query (if that matters). I want to order all group

Right to the questioin. I have articles that are being grouped by country. I'm counting the votes on every article from another table using a nested query (if that matters). I want to order all grouped results so that the article with most votes comes out, not the one that is the first found. I tried with HAVING, but seemes it doesn't work.

--|------------|----------|-----------|--
  | Article    |  Country |  Votes    |
--|------------|----------|-----------|--
  | Test art   |  1       |  2        |
--|------------|----------|-----------|--
  | Test ar2   |  2       |  3        |
--|------------|----------|-----------|--
  | Test ar3   |  1       |  6        |
--|------------|----------|-----------|--
  | Test ar4   |  1       |  4        |
--|------------|----------|-----------|--
  | Test ar5   |  2       |  9        |
--|------------|----------|-----------|--

So when grouped the result shoud be something like this:

--|------------|----------|-----------|--
  | Article    |  Country |  Votes    |
--|------------|----------|-----------|--
  | Test ar3   |  1       |  6        |
--|------------|----------|-----------|--
  | Test ar5   |  2       |  9        |
--|------------|----------|-----------|--

That's what I've made so far... with no result ;(

SELECT * ,  `sa`.`id` AS aid, (

SELECT SUM(  `svv`.`vote` ) AS smvt
FROM social_votes AS svv
WHERE  `svv`.`article_id` =  `sa`.`id` 
AND UNIX_TIMESTAMP(  `svv`.`date` ) 
BETWEEN  'SOMETIME'
AND  'SOMETIME'
O开发者_Python百科RDER BY smvt DESC
) AS sumvotes
FROM (
social_articles AS sa
)
JOIN social_countries AS sc ON sa.country = sc.id
GROUP BY sa.country
HAVING sumvotes = MAX( sumvotes ) 
ORDER BY RAND( ) DESC 
LIMIT 4

So I'll be gratefull if you can lay a hand :)


... order by country, votes desc


surround the whole query with brackets and select from it with the order by on the field you need...I mean

SELECT *, alias.field (...your_whole_query...) AS alias ORDER BY alias.field;
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号