Doubt with group by for last record

Question

I want to make a select that brings the grade data of each student, grouped by student and subject, but I want only the result of the last test of each subject. For example a student can take the English test 3 times, and the math test twice, in the results should show the data of the last English and math test.

The columns should be: student, class, date of completion of the last test and grade.

If I don’t put the "note" column in group by gives error, but if I put shows the results of all evidence, not just the last.

 select 
   aluno, aula, max(data) as data, nota
  from notas
  group by aluno, aula
  order by aluno, aula

Create and Insert scripts

CREATE TABLE [dbo].[notas](
    [aluno] [varchar](50) NULL,
    [aula] [varchar](50) NULL,
    [data] [datetime] NULL,
    [nota] [decimal](18, 1) NULL
) ON [PRIMARY]

GO

INSERT [dbo].[notas] ([aluno], [aula], [data], [nota]) VALUES (N'JOSE', N'ING1', CAST(N'2016-12-01 00:00:00.000' AS DateTime), CAST(4.0 AS Decimal(18, 1)))
INSERT [dbo].[notas] ([aluno], [aula], [data], [nota]) VALUES (N'CARLOS', N'ING1', CAST(N'2016-12-01 00:00:00.000' AS DateTime), CAST(6.0 AS Decimal(18, 1)))
INSERT [dbo].[notas] ([aluno], [aula], [data], [nota]) VALUES (N'JOSE', N'ING1', CAST(N'2016-12-05 00:00:00.000' AS DateTime), CAST(7.0 AS Decimal(18, 1)))
INSERT [dbo].[notas] ([aluno], [aula], [data], [nota]) VALUES (N'CARLOS', N'MAT', CAST(N'2016-12-04 00:00:00.000' AS DateTime), CAST(5.6 AS Decimal(18, 1)))
INSERT [dbo].[notas] ([aluno], [aula], [data], [nota]) VALUES (N'JOSE', N'ING1', CAST(N'2016-11-30 00:00:00.000' AS DateTime), CAST(4.0 AS Decimal(18, 1)))
INSERT [dbo].[notas] ([aluno], [aula], [data], [nota]) VALUES (N'JOSE', N'MAT', CAST(N'2016-11-30 00:00:00.000' AS DateTime), CAST(6.0 AS Decimal(18, 1)))
INSERT [dbo].[notas] ([aluno], [aula], [data], [nota]) VALUES (N'JOSE', N'MAT', CAST(N'2016-12-10 00:00:00.000' AS DateTime), CAST(8.0 AS Decimal(18, 1)))

Answer 1

With ROW_NUMBER():

SELECT * FROM (
SELECT *, ROW_NUMBER()
     OVER(PARTITION BY aluno, aula ORDER BY data DESC) as sequencia from notas
) F WHERE sequencia = 1

References:

• ROW_NUMBER (Transact-SQL)
• ROW_NUMBER - How To Use It
• How to dynamically Number lines in a Transact-SQL statement
• Trouble using ROW_NUMBER() OVER (PARTITION BY ...)
• Limit record occurrences for each type

Answer 2

I do not know if it is the query with better performance, but I believe I solved your problem:

select T1.* 
from notas T1
inner join (select aluno, aula, max(data) as data
            from notas
            group by aluno, aula
            ) T2 on T1.aluno = T2.aluno and T1.aula = T2.aula and T1.data = T2.data
Order by aluno, aula, data