SQL query comparing two fields from the same table

I have a table in SQL Server and I’m having a hard time making a query.

I have a table like this:

alunos

ra     nome     serie     cod_curso
23     joão     1         EI
23     joão     2         EI
23     joão     3         EI
44     maria    5         EF
44     maria    6         EF
61     jose     10        CCO
32     ana      7         PED
78     ana      8         PED

I need to select the highest value of serie for each ra . The result of the query should look like this:

alunos

ra     nome     serie     cod_curso
23     joão     3         EI
44     maria    6         EF
61     jose     10        CCO
78     ana      8         PED

If I select MAX(serie) but it will not work. I need to select MAX(serie) according to each ra , but I do not know how to do that.

You can do it this way:

select ra, nome, cod_curso, max(serie) as serie from alunos group by ra, nome, cod_curso

Another way without having to put all the fields in gruop by:

select a1.* from alunos as a1
inner join (
   select a2.ra, max(a2.serie) as serie
   from alunos as a2 group by a2.ra
) a2 on a1.ra = a2.ra and a1.serie = a2.serie

If serie is a number the Max() function will work, there is no mystery.

select ra, nome, max(serie), cod_curso from alunos group by  ra, nome, cod_curso

I do not know if I’m confusing here what you want … but you have two ana with different ra, being that you want only one ana would not the group by and have the result that you showed, but if they are different it would be easier …
See what’s below to get what you want …

declare @alunos table
(
 ra int,
 nome nvarchar(100),
 serie int,
 cod_curso nvarchar(100)
)

insert into @alunos
values 
(23,'joão',1,    'EI'),
(23,'joão',2,    'EI'),
(23,'joão',3,    'EI'),
(44,'maria',    5,    'EF'),
(44,'maria',    6,    'EF'),
(61,'jose',10,   'CCO'),
(32,'ana', 7,    'PED'),
(78,'ana', 8,    'PED')

select a.* from @alunos a
join(select nome,     max(serie) as serie,    cod_curso from @alunos
        group by nome,  cod_curso
    )d
on d.nome = a.nome
and d.serie = a.serie
and d.cod_curso = a.cod_curso

If it is the case of catching the biggest ra you can do it straight like this.

select max(ra) as ra, nome,     max(serie) as serie,    cod_curso from @alunos
group by nome,  cod_curso

Or if ana are different people use

select ra, nome,     max(serie) as serie,    cod_curso from @alunos
group by ra, nome,  cod_curso

Try using “GROUP BY ra” at the end of your query.

The GROUP BY will group your query by the column you want, in case the ra.