CODEWITHSUNDEEP
sqlsql-server
Hank Liu
Hank Liu

Reputation: 19

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

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

Upvotes: 1

Views: 151

Answers (6)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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().

Upvotes: 0

Anonymous
Anonymous

Reputation: 440

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))

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

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

Upvotes: 1

kc2018
kc2018

Reputation: 1460

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

Upvotes: 0

lincolnerson
lincolnerson

Reputation: 168

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.

Upvotes: 0

PSK
PSK

Reputation: 17943

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

Upvotes: 1

Related Questions