Get the first occurence of the result in each specified group

Question

I have this query in sql server 2012

select sum(user_number),
  sum(media_number),
 month_name from (
select TOP 100
    count(distinct a.answer_group_guid) as 'user_number',
  count(distinct a.media_guid) as 'media_number',
 datename(mm,answer_datetime) as 'month_name' ,year(answer_datetime) as 'year'
from
    tb_answers as a
        left outer join
    tb_media as m ON m.user_guid = 'userguid' and m.media_guid=a.media_guid
where
 m.user_guid = 'userguid'

group by concat(year(answer_datetime),'',month(answer_datetime)),datename(mm,answer_datetime),year(answer_datetime)

order by  year(answer_datetime) desc) as aa

group by month_name,year
order by  month_name desc,year desc;

it get this result Out

user_number  media_number   month_name

5                  1         September

2                  1         October

1                  1         October

1                  1         August

But I need only the first occurence of octuber month

as

user_number  media_number   month_name

5                  1         September

2                  1         October

1                  1         August

Answer 1

You simply need to use a ranking function like ROW_NUMBER(). Use it to number the records partitioning by month_name, and select only the records which are number 1 in each partition, i.e.

Add this to the select list of your query:

ROW_NUMBER() OVER(PARTITION BY month_name ORDER By XXX) as RowNumber

This will number the rows which have the same month_name with consecutive numbers, starting by 1, and in the order specified by XXX.

NOTE: specify the order in XXX to decide which of the month rows is number one and will be returned by the query

And then, do a select from the resulting query, filtering by RowNumber = 1

SELECT Q.user_number, Q.media_number, Q.month_name
FROM(
   -- your query + RowNumber) Q
WHERE Q.RowNumber = 1

NOTE: if you need some ordering in your result, you'll have to move the ORDER BY out of the subselect, and write it beside the WHERE Q.RowNumber=1