SQL query comparing two fields of the same table

Asked

Viewed 8,633 times

3

I have a table in SQL Server and I’m having trouble 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 consultation 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 won’t work. I need to select MAX(serie) according to each ra, but I don’t know how to do it.

sql sql-server

4 answers

4

Case serie be a number the function Max() will work, there’s no mystery. 

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

2


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

  • When I use this query, it returns this error: Column 'alunos.nome' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

  • Adds group by ra, name, cod_course at the end of the query.

  • But if my table has 30 columns I will have to put the 30 in group by?

  • Yes, I would need to, but see the other way I put it with Inner Join.

1

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 ra.

1

I don’t know if I’m confusing here what you want... but you have two ana with a different ra, and you only want one ana I wouldn’t want the group by to have the result you showed, but if they were different it would be easier... See how it gets down 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’s the case to catch the biggest ra you can do straight like this..

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

Or if the ana are different people use

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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.