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;
精彩评论