mysql 重複なし 結合して出現回数でソート

SELECT message, COUNT(*) AS COUNT
FROM chats
GROUP BY message
ORDER BY COUNT DESC

条件指定する場合

SELECT message, COUNT(*) AS COUNT
FROM chats
where created_at BETWEEN ('2018-12-01 00:00:00') AND ('2018-12-31 00:00:00')
GROUP BY message
ORDER BY COUNT DESC

余談：RailsのActiveRecordでやる場合

# ActiveRecord
Chat.select(:message,:created_at)
  .where(created_at: '2018-12-01'..'2018-12-31')
  .order('count_message desc')
  .group(:message)
  .count(:message)

結合して重複なしソート

SELECT user_status_id,SUM(score),facebook_id,fullname,phone,address
FROM results
INNER JOIN user_vote_product_profiles ON results.user_status_id = user_vote_product_profiles.id
GROUP BY user_status_id
ORDER BY SUM(score) DESC;

そっからランダム抽出

SELECT user_status_id,SUM(score),facebook_id,fullname,phone,address
FROM results
INNER JOIN user_vote_product_profiles ON results.user_status_id = user_vote_product_profiles.id
GROUP BY user_status_id
HAVING SUM(score) >= 100
ORDER BY RAND()
LIMIT 9;

今回はクイズアプリの成績上位者からランダムで景品の当選者を選抜するのにこれらSQLを使った

以上。