Unique person score by month

Question

I'm using MySQL here is my table named npstable

pk id | score | personid | date 
---------------------------------------
435 | 4    | 33       | 2012-01-05
436 | 10   | 78       | 2012-01-21
437 | 5    | 22       | 2012-05-11
438 | 2    | 33       | 2012-01-22
439 | 10   | 22       | 2012-05-25
440 | 4    | 33       | 2012-02-05

i want to get score of those persons who have not score in same month like i want

pk id | score | personid | date 
---------------------------------------
435 | 4    | 33       | 2012-01-05
436 | 10   | 78       | 2012-01-21
437 | 10   | 22       | 2012-05-25
440 | 4    | 33       | 2012-02-05

i am already using query with my other requirements like this

"select * from npstable where date_created>='2012-01-01' AND date_created<='2012-06-05' AND score BETWEEN 0 AND 10;"

Any ideas how to make select queries that can generate the my required report.

use SELECT DISTINCT(score) and look at this post -> http://stackoverflow.com/questions/5967130/mysql-select-one-column-distinct-with-corresponding-other-columns — SerhatCan, Jul 16 '14 at 08:22
Duplicate of http://stackoverflow.com/questions/16979136/mysql-select-distinct-records-from-latest-dates-only — ahruss, Jul 16 '14 at 08:22
Check out your require output. Did you think last row is correct? — Shaikh Farooque, Jul 16 '14 at 08:27
What is the rule that says personid 33, score 2 (recordid 536) should not be in the output? Is it because you want the best score for each person for each month? — Hugh Jones, Jul 16 '14 at 08:29

FuzzyTree · Accepted Answer · 2014-07-16T08:36:37.637

This query selects the earliest record per month for each personid

select t1.*
from npstable t1
join (
    select 
        personid, year(date) my_year, 
        month(date) my_month, min(date) min_date
    from npstable t2
    group by person_id, my_year, my_month
) t2 join on year(t1.date) = t2.my_year 
          and month(t1.date) = t2.my_month
          and t1.date = t2.min_date
          and t1.personid = t2.personid

another version that selects rows with the lowest id per personid per month

select t1.*
from npstable t1
where id in (select 
    min(id) from npstable t2
    group by personid, month(date), year(date)
)

Unique person score by month

1 Answers1