Doubt with group by for last record

Question

Doubt with group by for last record

Navigation

#1 by (3 votes)
#2 by (2 votes)

1

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

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

If I do not put the "note" column in group by it gives error, but if I put shows the results of all the tests, not just the last one.

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

sql sql-server

asked by anonymous 12.12.2016 / 15:11

2 answers

3

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:

12.12.2016 / 15:38

Delete file on client host Subtract date with javascript

score 2 · Accepted Answer

I do not know if it's the best performing query, but I think I've 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