Select max in nested queries

Question

Select max in nested queries

Navigation

#1 by (1 votes)

3

I have to select the candidate who has more publications than any of the other candidates.

select candidato.nome, Count(1) as nr_publicacoes 
from candidato, cv,ficha_inscricao, publicacao_cv 
where candidato.bi = ficha_inscricao.bi and 
      candidato.bi = cv.bi and cv.cod_cv = publicacao_cv.cod_cv 
group by candidato.nome;

But when I max, Orale SQL Developer does not give results or error messages. I do not want to use rownum , my goal is to solve using max .

sql oracle sqldeveloper

asked by anonymous 20.11.2015 / 10:06

1 answer

Creating GIF images [closed] Select without the id field (RoR)

score 1 · Accepted Answer

In chat conversation, @DanielaMaia said that ROWNUM could not be used, only the MAX. So the original response has been replaced by this now.

To avoid using ROWNUM, a Common Table Expression (CTE) can be used, as shown below:

with publicacao_cte (nome,nr_publicacoes)
AS
(
    select
        candidato.nome,
        Count(1) as nr_publicacoes
    from
        candidato,
        cv,
        ficha_inscricao,
        publicacao_cv
    where
        candidato.bi = ficha_inscricao.bi and
        candidato.bi = cv.bi and
        cv.cod_cv = publicacao_cv.cod_cv
    group by 
        candidato.nome
)

select nome
from publicacao_cte
where publicacao_cte.nr_publicacoes = (select max(nr_publicacoes) from publicacao_cte);