Get highest highscore entries between given dates

Question

There is a scores_score table which contains following columns:

id, player_name, value, created_at

I have to fetch N (100) best scores where:

1. player_name must be unique across results
2. only best score for given player_name should be returned
3. results have to be filtered by date range

Lets say I have following data:

id      player_name    value          date
1       A               400        2016-09-10
2       B               200        2016-09-12
3       C               400        2016-09-15
4       C               500        2016-09-14
5       B               100        2016-09-20
6       A               6000       2015-01-01
7       B               1200       2016-09-29

And want to get best players with their scores between 2016-09-01 and 2016-09-20. I should get the:

id      player_name    value          date
4       C               500        2016-09-14
1       A               400        2016-09-10
2       B               200        2016-09-12

This is my approach to solve it, but there is an issue in nested SELECT as it fetches the best score of the player overall not within date ranges.

SELECT b.*, a.*
FROM (SELECT player_name, max(value) AS max_value
      FROM scores_score
      GROUP BY player_name
      ORDER BY max(value) DESC) a
INNER JOIN scores_score b ON a.player_name = b.player_name AND a.max_value = b.value
WHERE CAST(b.created_at AS DATE) >= %(date_border)s
ORDER BY b.value DESC
LIMIT 100

Answer 1

distinct on

select *
from (
    select distinct on (player_name) *
    from scores_score
    where date between '2016-09-01' and '2016-09-20'
    order by player_name, value desc
) s
order by value desc
limit 100