0

I have a table where I record multiple scores from users daily. I'm trying to create a query where I get the distinct top 5 weekly winners for each week that passes...

Is it that I need to do a sub query grouping results in both max score and date week? or do I need to do 2 sub queries one for the date another for max score then use the outer query to group?

Well the table structure would be: 

NAME, 
SCORE, 
DATE

I came up with this 

SELECT * 
FROM `highscores` 
WHERE id IN ((SELECT id 
              FROM highscores 
              WHERE WEEK(date) IN (SELECT DISTINCT WEEK(date) 
                                   FROM highscores) 
              ORDER BY score DESC)) 
GROUP BY email 
ORDER BY date, score DESC

But apparently I can't use LIMIT in sub-queries

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kendall
  • 5,065
  • 10
  • 45
  • 70
  • In postgresql you could use a window function but in MySQL you don't have them, you have the less powerful GROUP_CONCAT() instead. Maybe check http://code.openark.org/blog/mysql/sql-selecting-top-n-records-per-group-another-solution . -- Also, can you post some sample rows of your table? – Daniel Sparing Feb 12 '14 at 23:54
  • Can you post table(s) structure with sample data, and expected output? – cyadvert Feb 12 '14 at 23:57

1 Answers1

1

I think this should work for you. It should also bring back ties, in the event there are any (let's say the 5th highest score for one week were a tie between 2 people, this would bring them both back for that week, and so you'd have 6 rows for that week).

select *
  from highscores x
 where x.score >=
       (select max(e.score)
          from highscores e
         where week(e.date) = week(x.date)
           and e.score <
               (select max(d.score)
                  from highscores d
                 where week(d.date) = week(x.date)
                   and d.score <
                       (select max(c.score)
                          from highscores c
                         where week(c.date) = week(x.date)
                           and c.score <
                               (select max(b.score)
                                  from highscores b
                                 where week(b.date) = week(x.date)
                                   and b.score <
                                       (select max(a.score)
                                          from highscores a
                                         where week(a.date) = week(x.date))))))
 order by date, score desc
Brian DeMilia
  • 13,103
  • 1
  • 23
  • 33
  • Good effort here but for me this looks like a bit much and sorted it out outside of MySQL. Thanks – Kendall Feb 13 '14 at 16:11