Conditional for record selection

1

I am having a question for selecting a record from a table and would like some help.

An example of the structure of the following table:

idhistorico_profissional | idprofessional | function | beginning | end | situation

 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, when using max (), always returns the values with date. For example, in the case of the professional 77 the last function of it is of sale, which it still works currently, but the search with max () returns me the function of telemkt.

Any ideas how to do this?

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
    
asked by anonymous 14.12.2016 / 01:43

3 answers

2
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 
)
    
14.12.2016 / 02:28
0

"max" on the start date.     SELECT DISTINCT *, professional ID, function, Max (Start)     GROUP BY idprofissional

    
14.12.2016 / 01:57
0

is returning the phone in the place of sales because the phone has date and sales is null the max function will consider the date in this case

For the data sample you can do the following, look for the ones that are 0 and then those will be current (last) and use max for those that are situation other than 0

    
14.12.2016 / 02:50