Getting unique months using group by

Question

I have a table called tbl_points with the following columns:

[key] identity
[fkey] int -> forign key from other table
[points] int -> number
[inputdate] datetime -> getdate()

And values like:

key,fkey,points,inputdate

1,23,5,20170731
2,23,2,20170801
3,23,4,20170801
4,25,2,20170801
5,25,2,20170802
6,23,5,20170803
7,25,3,20170803
8,23,5,20170804

I am executing a query like this:

select fkey,sum(points) points,month(inputdate) mnd,year(inputdate) yy
from tbl_points 
group by fkey,month(inputdate) mnd,year(inputdate)
order by year(inputdate),month(inputdate) mnd,points

Which gives as result:

fkey,points,mnd,yy

23,14,8,2017
25,7,8,2017
25,5,7,2017

So far so good. Now I want only the top 1 of each month, so

23,14,8,2017
25,5,7,2017

I can do this in the code, or in the stored procedure with a temporary table or cursor.

But perhaps there is is simpler solution. Any ideas? Or a better approach?

Does [this](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group?rq=1) link from the Related questions on the right side of the page help? — HABO, Aug 03 '17 at 13:53
Perhaps use ranking function ROW_NUMBER() then use WHERE clause to return only rows with value 1. — SAS, Aug 03 '17 at 14:03

score 0 · Answer 1 · answered Aug 03 '17 at 14:38

DECLARE @tbl_points TABLE
    (
      [key] INT ,
      [fkey] INT ,
      [points] INT ,
      [inputdate] DATETIME
    );

INSERT  INTO @tbl_points
VALUES  ( 1, 23, 5, '2017-07-31' ),
        ( 2, 23, 2, '2017-08-01' ),
        ( 3, 23, 4, '2017-08-01' ),
        ( 4, 25, 2, '2017-08-01' ),
        ( 5, 25, 2, '2017-08-02' ),
        ( 6, 23, 5, '2017-08-03' ),
        ( 7, 25, 3, '2017-08-03' ),
        ( 8, 23, 5, '2017-08-04' ); 


/* Your query */
SELECT  fkey ,
        SUM(points) points ,
        YEAR(inputdate) [year] ,
        MONTH(inputdate) [month]
FROM    @tbl_points
GROUP BY fkey ,
        MONTH(inputdate) ,
        YEAR(inputdate)
ORDER BY YEAR(inputdate) ,
        MONTH(inputdate) ,
        points;


/* Query you want */
SELECT  s.fkey ,
        s.points ,
        s.[year] ,
        s.[month]
FROM    ( SELECT    fkey ,
                    SUM(points) points ,
                    YEAR(inputdate) [year] ,
                    MONTH(inputdate) [month] ,
                    ROW_NUMBER() OVER ( PARTITION BY MONTH(inputdate) ORDER BY YEAR(inputdate) , MONTH(inputdate) , SUM(points) ASC ) [Row]
          FROM      @tbl_points
          GROUP BY  fkey ,
                    MONTH(inputdate) ,
                    YEAR(inputdate)
        ) AS s
WHERE   s.Row = 1;

Result:

Perhaps you can also explain the key difference so that OP learns why your code works. — Zabuzard, Aug 03 '17 at 16:13
You mean by the key difference the field 'fkey'? In this case the fkey relate to another table (tbl_stories) where it is the identity. People can give points to story's and every month there will be 1 story on top. — Jixaw, Aug 03 '17 at 17:35

Getting unique months using group by

1 Answers1