How to assemble a consult to bring the latest value based on month and year?

Asked

Viewed 59 times

0

SELECT DadosFinanceirosFiliado_Valor,
       Filiados_Filiado_Id,
       DadosFinanceirosFiliado_CompetenciaMes,
       DadosFinanceirosFiliado_CompetenciaAno
  FROM dadosfinanceirosfiliado t
 INNER JOIN filiados
    ON dadosfinanceirosfiliado.Filiados_Filiado_Id = filiados.Filiado_Id
 WHERE t.DadosFinanceirosFiliado_Valor(SELECT Filiados_Filiado_Id 
                                         FROM dadosfinanceirosfiliado 
                                        WHERE DadosFinanceirosFiliado_Valor = t.DadosFinanceirosFiliado_Valor 
                                        ORDER BY DadosFinanceirosFiliado_CompetenciaMes, DadosFinanceirosFiliado_CompetenciaAno DESC LIMIT 0, 1)

I’m having a hard time putting together my consultation, I wanted to know how to assemble a select to bring the last value of a table based on month and year (the most I could get was in the sql shown above).

Table where the query happens: inserir a descrição da imagem aqui

php mysql sql

  • if you use the MAX(Data) function it searches for the last date value.

  • But beyond the month has the year also friend, as it would work better has some example ??

  • I think it would be easier if you showed some example of the two tables you linked before. Why Inner Join exists if you have no condition linked to this table?

  • pq the column Affiliate_affiliate_id comes from another table it and an index

1 answer

0

Whereas the query shown in the question is correct in relation to the relationship between fields, you can concatenate (Concat()) year and month and make a decreasing ordering of this result (ex 201812, 201801), limiting the return on one, i.e., taking only the highest value; type like this:

SELECT DadosFinanceirosFiliado_Valor, Filiados_Filiado_Id, DadosFinanceirosFiliado_CompetenciaMes, DadosFinanceirosFiliado_CompetenciaAno
FROM dadosfinanceirosfiliado t
INNER JOIN filiados f ON t.Filiados_Filiado_Id = f.Filiado_Id
ORDER BY CONCAT(DadosFinanceirosFiliado_CompetenciaAno, DadosFinanceirosFiliado_CompetenciaMes) DESC LIMIT 1

edited

as comments, the idea is to bring the last value of each Filiados_Filiado_Id; thus, it will be necessary to add a subconsultation for filtering by affiliated. For this, the field of id tables (I believe they have an identifier, you just need to validate the name of this field):

SELECT DadosFinanceirosFiliado_Valor, Filiados_Filiado_Id, DadosFinanceirosFiliado_CompetenciaMes, DadosFinanceirosFiliado_CompetenciaAno
FROM dadosfinanceirosfiliado t
INNER JOIN filiados f ON t.Filiados_Filiado_Id = f.Filiado_Id
WHERE f.id in (SELECT f2.id
               FROM filiados f2
               WHERE f2.Filiado_Id = f.Filiado_Id
               ORDER BY CONCAT(DadosFinanceirosFiliado_CompetenciaAno, DadosFinanceirosFiliado_CompetenciaMes) DESC LIMIT 1)

  • It didn’t work ta joining all table data

  • @Visiondevelopment, as well as "all data"? The Join will put it all together, who will say what should be displayed or is not what comes in select. Now if there is any filtering condition, you have to go to the where (in this case you need to specify because the question is not clear).

  • Type exists several Affiliate_affiliate_id, type 11 and 12 ai wanted the last id value 11 and 12 , understand ?

  • in the case is the last for each Filiados_Filiado_Id?

  • that the last value of each Affiliate_affiliate_id is based on month and year

  • @Visiondevelopment see if editing solves the problem ;)

  • gave this error Error Code: 1235 This version of Mysql doesn’t yet support 'LIMIT & IN/ALL/ANY/SOME/SOME subquery', and otherwise I didn’t understand the F2 part

Show 2 more comments

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.