CODEWITHSUNDEEP
sql
IlConte
IlConte

Reputation: 159

TOP 5 scores by month (month is a varchar)

Suppose I have this table

article | score | date (VARCHAR)
A           10      2010/01
B           10      2010/01
C           10      2010/01
D           10      2010/01
E           10      2010/01
F            1      2010/01
G            1      2010/01
H            1      2010/01
B            9      2010/01
B            9      2010/01
A            4      2010/02
C           10      2010/02
G           10      2010/02
G            2      2010/02

and I would to return the TOP 5 scores with distinct date.

the OUTPUT I like:

article |  average |  date
A            10       2010/01
B           9.33      2010/01
C            10       2010/01
D            10       2010/01
E            10       2010/01
A            4        2010/02
....        ...        .....

i try some queries with few result.

SELECT
    article as articlex,
    date as datex,
    (SELECT avg(score) FROM test_anna WHERE article = articlex AND date = datex) as average
FROM test_anna GROUP BY articlex, average ORDER BY datex ASC, articlex ASC

How do I do this using mysql statement? Thanks!

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

One method uses row_number() or rank() (depending on how you want to handle ties):

select t.*
from (select t.*,
             row_number() over (partition by date order by score desc) as seqnum
      from test_anna t
     ) t
where seqnum <= 5;

Upvotes: 1

Related Questions