Select MAX or SUM-CodePudding

enter image description here

Simply, I have exam note for many Student for many exam, see the picture below (MATH = 0, BIOLOGY = 2, ALGEBRA = 1) I just want to give the student the Max notes = The Student have 2 = on BIOLOGY so ALGEBRA AND MATH must be at 2

Try to have this :

enter image description here

I try this :

SELECT First_Name, EXAM, MAX(NOTE)
FROM My_Table
Group by First_Name, EXAM

Not working, still give me this (MATH = 0, BIOLOGY = 2, ALGEBRA = 1)

Try also :

SELECT First_Name, EXAM, 
CASE
  WHEN SUM(NOTE) <> 0 THEN MAX(NOTE)
Else 0
END AS MAX_NOTE
FROM My_Table

Not working

Please do you have any idea ? or solution ? Click to check see the picture (screenshot)

CodePudding user response：

Remove the group by, and use a window function to take care of per-student logic:

SELECT First_Name, EXAM, MAX(NOTE) over (partition by First_Name)
FROM My_Table

CodePudding user response：

SELECT 
    First_Name, 
    EXAM, 
    MAX(NOTE) OVER(partition by First_Name) as MAX_NOTE
FROM 
    My_Table

Reference for using Max and OVER together in SQL Server: https://docs.microsoft.com/en-us/sql/t-sql/functions/max-transact-sql?view=sql-server-ver15