1
\$\begingroup\$

I need this kind of ORDER BY to list my curriculum vitae data: when ep.data_termino is null it means that the job is your current job (you can have multiple current jobs), then I must list them first. Then, I need to list all the others ordered by the ep.data_termino ASC.

The IF was my only choice. I know that it is never a good option to use IFs in queries (at least that's how I learned it), so I am asking here if there is any better solution.

    SELECT ep.nome_empresa, ep.data_inicio, ep.descricao, ct.nome as contratacao_tipo_nome, p.nome as profissao_nome, 

IF(ep.data_termino IS NULL,NOW(),ep.data_termino) as data_term

    FROM experiencia_profissional as ep
    JOIN contratacao_tipo as ct ON ct.codigo = ep.codigo_contratacao_tipo
    JOIN profissao as p ON p.codigo = ep.codigo_profissao
    JOIN experiencia_profissional_membro as epm ON epm.codigo_experiencia_profissional = ep.codigo
    JOIN membro as m ON m.codigo = epm.codigo_membro
    JOIN usuario as u ON u.codigo_membro = m.codigo

    WHERE u.codigo = 124
    ORDER by data_term DESC, ep.data_inicio ASC
\$\endgroup\$
2
\$\begingroup\$

Try:

ORDER BY ep.data_termino IS NOT NULL, ep.data_termino DESC, ep.data_inicio ASC

BTW,

IFNULL(ep.data_termino,NOW()) 

is a cleaner way to express:

IF(ep.data_termino IS NULL,NOW(),ep.data_termino)
\$\endgroup\$
  • \$\begingroup\$ Wow that was awesome! thank you! =) and thanks for the tip on the IFNULL, i can't +1 you for this tip, sorry =X \$\endgroup\$ – Michel Ayres Feb 10 '12 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.