Mysql - most recent enter for each id

Question

I have a query that retrieve information from 5 tables. The relation between them is showed in the picture attached. I have created a query to do this, and so far so good. The issue is each user can have 1 or more entries at "PROFISSIONAL_PORCENTAGEM" table. My query is retrieving all them. I want the last one based on the date field "PROFISSIONAL_PORCENTAGEM_DATA". I´ve performed a research about this issue and the solution of "retrieve the most recent of each id" is the same when we are using only one table see example here MySQL - Selecting the most recent post by each of the 10 most recent authors. But in this case I have 5 tables. I dont know how to do that. Any idea will be appreciated.

SELECT
  PP.PROFISSIONAL_PORCENTAGEM_Pk,
  UST.USER_NAME, 
  ADDR.ADDRESS_NOME, 
  ADDR.ADDRESS_ATIVO,
  PA.PROFESSIONAL_ADDRSS_PK, 
  PP.PROFISSIONAL_PORCENTAGEM_PORCENT,
  PP.PROFISSIONAL_PORCENTAGEM_FK_CLINICA,
  PP.PROFISSIONAL_PORCENTAGEM_DATA

FROM 
  profissional_porcentagem AS PP
  RIGHT JOIN professional_addrss AS PA
    ON(PP.PROFISSIONAL_PORCENTAGEM_FK_CLINICA=PA.PROFESSIONAL_ADDRSS_PK)

  INNER JOIN address ADDR
    ON(PA.PROFESSIONAL_ADDRSS_FKADDRSS=ADDR.ADDRESS_Pk)

  INNER JOIN professional PF
    ON(PA.PROFESSIONAL_ADDRSS_FKPROFESSIONAL=PF.PROFESSIONAL_Pk)

  INNER JOIN usert AS UST
    ON(PF.PROFESSIONAL_FKUSER=UST.USER_Pk)

WHERE UST.USER_Pk=3
AND ADDR.ADDRESS_ATIVO=1;

sorry I need 10 reputation to attach image!! This is the image link http://imgur.com/ZP3OdeX

You should be able to upload your image to [imgur](http://www.imgur.com) and provide a link. — Kermit, Sep 23 '14 at 14:56
can you try to add group by user having max(PROFISSIONAL_PORCENTAGEM_DATA), not sure, just quick answer — gorgi93, Sep 23 '14 at 15:03
Thanks Kermit. I did know about that possibility. Now you can see the database image. — IgorAlves, Sep 23 '14 at 15:03
Hi gGololicic, i've try GROUP BY PROFISSIONAL_PORCENTAGEM_DATA, but the returns is not good. And if I use MAX do not work too, as Myles suggested. — IgorAlves, Sep 23 '14 at 15:11

score 1 · Accepted Answer · edited May 23 '17 at 12:22

Well, finally I found the solution. Just add this code to the end of sql query:

AND PP.PROFISSIONAL_PORCENTAGEM_Pk = (
SELECT t2.PROFISSIONAL_PORCENTAGEM_Pk FROM profissional_porcentagem as t2
    where t2.PROFISSIONAL_PORCENTAGEM_FK_CLINICA = PP.PROFISSIONAL_PORCENTAGEM_FK_CLINICA 
    order by t2.PROFISSIONAL_PORCENTAGEM_Pk desc limit 1
)

And then we gonna have the entire query like this:

SELECT
PP.PROFISSIONAL_PORCENTAGEM_Pk,
UST.USER_NAME, 
ADDR.ADDRESS_NOME, 
ADDR.ADDRESS_ATIVO,
PA.PROFESSIONAL_ADDRSS_PK, 
PP.PROFISSIONAL_PORCENTAGEM_PORCENT,
PP.PROFISSIONAL_PORCENTAGEM_FK_CLINICA,
PP.PROFISSIONAL_PORCENTAGEM_DATA

FROM 
profissional_porcentagem AS PP
RIGHT JOIN professional_addrss AS PA
    ON(PP.PROFISSIONAL_PORCENTAGEM_FK_CLINICA=PA.PROFESSIONAL_ADDRSS_PK)

INNER JOIN address ADDR
    ON(PA.PROFESSIONAL_ADDRSS_FKADDRSS=ADDR.ADDRESS_Pk)

INNER JOIN professional PF
    ON(PA.PROFESSIONAL_ADDRSS_FKPROFESSIONAL=PF.PROFESSIONAL_Pk)

INNER JOIN usert AS UST
    ON(PF.PROFESSIONAL_FKUSER=UST.USER_Pk)

WHERE UST.USER_Pk=3
AND ADDR.ADDRESS_ATIVO=1

AND PP.PROFISSIONAL_PORCENTAGEM_Pk = (
SELECT t2.PROFISSIONAL_PORCENTAGEM_Pk FROM profissional_porcentagem as t2
    where t2.PROFISSIONAL_PORCENTAGEM_FK_CLINICA =   PP.PROFISSIONAL_PORCENTAGEM_FK_CLINICA 
    order by t2.PROFISSIONAL_PORCENTAGEM_Pk desc limit 1
);

Note: I was inspired by this post Select row with most recent date per user

Mysql - most recent enter for each id

1 Answers1

Linked