Conditional for registration selection

Question

am with a question for selecting a record from a table and would like a help.

An example of the structure of the following table:

idhistorico_professional | idprofessional | funcao | inicio | final | situacao

 9     77       telemkt    2013-12-01    2014-05-01      NULL
 10    77       venda      2016-06-01       NULL          0
 11    78       pweb       2012-03-01    2014-06-01      NULL
 12    79       program    2014-02-01    2016-08-01      NULL

I have a problem to select the last function exercised, when using max(), always returns the values with date. For example, in the case of professional 77 his last job is for sale, which he still works currently, but the search with max() returns me the telemkt function.

Any idea how I could do that?

Follow the query:

SELECT profissional.nome, funcao, MAX(final) from historico_profissional 
        LEFT JOIN profissional on historico_profissional.idprofissional = 
        profissional.idprofissional GROUP BY historico_profissional.idprofissional

Answer 1

SELECT profissional.nome, funcao, final FROM historico_profissional 
LEFT JOIN profissional ON 
     historico_profissional.idprofissional = profissional.idprofissional
WHERE idhistorico_profissional IN 
(
    SELECT max(idhistorico_profissional) FROM historico_profissional 
    GROUP BY idprofissional 
)

Answer 2

Do the "max" on the start date. SELECT DISTINCT *, idprofissional, funcao,Max(Inicio) GROUP BY idprofissional

Answer 3

is returning telemkt in place of sales pq telemkt has date and sales is null the function max will consider the date in that case

by the data sample you can do the following, look for the ones that the situation is 0 and, soon these will be the current (last) and use max for situations other than 0