How to condition select in another table row in Mysql?

Question

I’m a beginner when it comes to Mysql language and I’m trying to filter some information I need to get out of a table.

Basically I have data of individuals in consecutive years (2003, 2004, 2005) all in the same table. In addition to these data, I have information, for example, on schooling (which may change year after year, or remain constant).

But some records in my database are inconsistent, for example, selecting the first records ordered by Cpf and by year, we have (I changed Cpf for reasons of data secrecy):

Ano      CPF        Escolaridade 
2004    10000000kkk 3
2005    10000000kkk 5
2003    10000000zzz 7
2005    10000000zzz 6
2003    10000000ttt 7
2005    10000000ttt 4
2004    10000000ppp 6
2005    10000000ppp 9
2004    10000000sss 7
2005    10000000sss 9
2005    10000000sss 9
2003    10000002xxx 6
2005    10000002xxx 7

The schooling is coded:

ANALFABETO      1
ATE 5.A INC     2
5.A CO FUND     3
6. A 9. FUND    4
FUND COMPL      5
MEDIO INCOMP    6
MEDIO COMPL     7
SUP. INCOMP     8
SUP. COMP       9
MESTRADO        10
DOUTORADO       11
IGNORADO        -1

My question is: how can I filter out these inconsistencies? Can anyone help me? I tried some querys but they didn’t work:

update `id2003to2005` set `chave_2`= concat( pis, grau_instr);

select
   k1.ano,  k1.grau_instr, k1.chave_2, k2.pis, k2.ano,  k2.grau_instr, k2.chave_2, k3.pis    
from id2003to2005 k1
   inner join id2003to2005 k2 on  k1.chave_2 != k2.chave_2 
   inner join id2003to2005 k3 on k2.pis=k3.pis 
where ((k1.ano - k2.ano=1) and ((k1.grau_instr - k2.grau_instr>1 or k1.grau_instr - k2.grau_instr<0))
    or ( (k1.ano - k2.ano>1) and (k1.grau_instr - k2.grau_instr>2 or k1.grau_instr - k2.grau_instr<0)))

Answer 1

Taking his example with the CPF "10000000zzz", schooling in 2003 was higher than in 2005.

2003    10000000zzz 7
2005    10000000zzz 6

If you want to catch this kind of inconsistency, I would recommend you create a consolidated temporary table the past year and note

select MAX(ano) as ano, CPF, 0 as valor_atual, 0 as valor_anterior 
into tabela_temporaria
from minha_tabela group by CPF

In this table you have the last year of CPF. Based on it, you will update the temporary table with the grade of that year

update tabela_temporaria set valor_atual = (SELECT escolaridade from minha_tabela WHERE ano = T.ano and CPF = T.CPF LIMIT 1)
from tabela_temporaria as T

And then update the temp with the grade of the first year before.

update tabela_temporaria set valor_anterior = (SELECT escolaridade from minha_tabela WHERE ano < T.ano and CPF = T.CPF ORDER BY ano DESC limit 1)
from tabela_temporaria as T

Comparing the two values you can list the Cpfs with this type of inconsistency:

select * from tabela_temporaria WHERE valor_anterior > valor_atual