How can I select an ID each month with the highest Mark

Question

I am fairly new to SQL. My table is

   id     mark  datetimes      
    ------|-----|------------
    1001  | 10  | 2011-12-20   
    1002  | 11  | 2012-01-10 
    1005  | 12  | 2012-01-10  
    1003  | 10  | 2012-01-10 
    1004  | 11  | 2018-10-10 
    1006  | 12  | 2018-10-19  
    1007  | 13  | 2018-03-12  
    1008  | 15  | 2018-03-13

I need to select an ID with the highest mark at the end of each month (Year also matters) and ID can be repeated

My desired output would be

    id    mark  
    -----|----
   1001  | 10
   1005  | 12
   1006  | 12
   1008  | 15

So far I've Only able to get the highest value in each month

 Select Max(Mark)'HighestMark' 
 From StudentMark
 Group BY Year(datetimes), Month(datetimes)

When I tried to

 Select Max(Mark)'HighestMark', ID 
 From StudentMark
 Group BY Year(datetimes), Month(datetimes), ID

I get

Id          HighestMark
----------- ------------
1001        10
1002        11
1003        12
1004        10
1005        11
1006        12
1007        13
1008        15

[Classic duplicate of a](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) [tag:greatest-n-per-group] question. Note, however, that leaving off the partition (year/month combination) from the final results makes them mysterious. — Clockwork-Muse, Mar 18 '18 at 05:57
@Clockwork-Muse No, this isn't a duplicate of that question, if I read correctly. Otherwise, the OP's `GROUP BY` query should already be sufficient, which it apparently isn't. — Tim Biegeleisen, Mar 18 '18 at 07:33
@TimBiegeleisen - then why are the answers functionally the same? — Clockwork-Muse, Mar 19 '18 at 16:21

Tim Biegeleisen · Answer 1 · 2018-03-18T07:30:52.227

I don't see a way of doing this in a single query. But we can easily enough use one subquery to find the final mark in the month for each student, and another to find the student with the highest final mark.

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY ID, CONVERT(varchar(7), datetimes, 126)
            ORDER BY datetimes DESC) rn
    FROM StudentMark
)

SELECT ID, Mark AS HighestMark
FROM
(
    SELECT *,
        RANK() OVER (PARTITION BY CONVERT(varchar(7), datetimes, 126)
            ORDER BY Mark DESC) rk
    FROM cte
    WHERE rn = 1
) t
WHERE rk = 1
ORDER BY ID;

Demo

PSK · Accepted Answer · 2018-03-18T05:27:04.220

You can try like following.

Using ROW_NUMBER()

SELECT * FROM
(
  SELECT *, 
   ROW_NUMBER() OVER(PARTITION BY YEAR(DATETIMES)
               ,MONTH(DATETIMES) ORDER BY MARK DESC) AS RN    
 FROM [MY_TABLE]
 )T WHERE RN=1

Using WITH TIES

SELECT TOP 1 WITH TIES ID, mark AS HighestMarks
FROM [MY_TABLE]
ORDER BY ROW_NUMBER() OVER (PARTITION BY YEAR(datetimes)
                     ,MONTH(datetimes) ORDER BY mark DESC)

Example:

WITH MY AS
(

SELECT
* FROM (VALUES
    (1001  , 10  , '2011-12-20'),
    (1002  , 11  , '2012-01-10'),
    (1005  , 12  , '2012-01-10'),
    (1003  , 10  , '2012-01-10'),
    (1004  , 11  , '2018-10-10'),
    (1006  , 12  , '2018-10-19'),
    (1007  , 13  , '2018-03-12'),
    (1008  , 15  , '2018-03-13')
    ) T( id , mark , datetimes)     
)

SELECT ID,Mark as HighestMark FROM
(
  SELECT *, 
  ROW_NUMBER() OVER(PARTITION BY YEAR(DATETIMES),MONTH(DATETIMES) ORDER BY MARK DESC) AS RN    
 FROM MY
 )T WHERE RN=1

Output:

ID    HighestMark
1001    10
1005    12
1008    15
1006    12

Much the best solution. It is beyond me why people would look to using string functions on dates when built-in date functions do the same thing. — Gordon Linoff, Mar 18 '18 at 11:13
Although Row_Number() is beyond what I've learned, and it took me a quick google to figure out what exactly is going on. All in all, this is very elegant, Thanks — Hank Liu, Mar 18 '18 at 21:56

kc2018 · Answer 3 · 2018-03-18T06:17:37.540

0

Use RANK in case there are more than 1 student having the same highest mark.

select id, mark
from
(select *,  
 rank() over( partition by convert(char(7), datetimes, 111) order by mark desc) seqnum
from studentMark ) t
where seqnum = 1

edited Mar 18 '18 at 06:17

answered Mar 18 '18 at 05:27

kc2018

1,440
1
8
8

score 0 · Answer 4 · answered Mar 18 '18 at 06:12

In below query you have included ID column for Group By, because of this, it is considering all data for all ID.

Select Max(Mark)'HighestMark', ID From StudentMark Group BY Year(datetimes), Month(datetimes), ID

Remove ID column from this script and try again.

Anonymous · Answer 5 · 2018-03-18T08:38:58.527

this should work:

    select s.ID, t.Mark, t.[Month year] from Studentmark s
    inner join (
    Select 
Max(Mark)'HighestMark'
,cast(Year(datetimes) as varchar(10)) +  
 cast(Month(datetimes) as varchar(10)) [month year]
     From StudentMark
     Group BY cast(Year(datetimes) as varchar(10)) 
           +  cast(Month(datetimes) as varchar(10))) t on t.HighestMark = s.mark and  
t.[month year] = cast(Year(s.datetimes) as varchar(10)) +  cast(Month(s.datetimes) as varchar(10))

score 0 · Answer 6 · answered Mar 18 '18 at 11:16

If for some reason you abhor subqueries, you can actually do this as:

select distinct
      first_value(id) over (partition by year(datetimes), month(datetime) order by mark desc) as id
      max(mark) over (partition by year(datetimes), month(datetime))
from StudentMark;

Or:

select top (1) with ties id, mark
from StudentMark
order by row_number() over (partition by year(datetimes), month(datetime) order by mark desc);

In this case, you can get all students in the event of ties by using rank() or dense_rank() instead of row_number().

How can I select an ID each month with the highest Mark

6 Answers6

Demo