Select last record of a table - sql

Question

Good afternoon! I have a table with several records of several patients. I need to bring only the last record of each patient. For example in the table:

REG        PACIENTE          TITULO                  DATA

1000       joão              evolução               01/02/2018

1000       joão              admissão               20/01/2018

1000       joão              evolução               30/01/2018

2000       maria             evolução               02/02/2018

2000       maria             evolução               01/02/2018

2000       maria             admissão               20/01/2018

2000       maria             admissão               05/01/2018

No select would have to bring the evolution of John of the day 01/02/2018 (most current record) and Mary evolution of the day 02/02/2018

I am using max(data) but in the result comes the last evolution and the last admission of each patient, as if the max data is working for each TYPE of record and not per patient.

I’m using the MAXDB

my select was as follows, in fact I am putting together some tables, and I had to group all the items I am looking for

  SELECT RCL.RCL_PAC,
   PAC.PAC_NOME,
   SMK.SMK_ROT,
   HSP.HSP_DTHRE,
   HSP.HSP_DTHRA,
   HSP.HSP_STAT,
   HSP.HSP_NUM,
   HSP.HSP_pac,
   HSP.HSP_LOC,
   LOC.LOC_NOME,
   case WHEN  HSP.HSP_DTHRA is null then DATEDIFF(HSP.HSP_DTHRE, now()) else DATEDIFF(HSP.HSP_DTHRA,HSP.HSP_DTHRE)  end dias,
   MAX(DATE(RCL.RCL_DTHR)) AS MAIOR_DATA ,
   LOC.LOC_STR,
   STR.str_nome,
   RCL.RCL_STAT,
   substr(RCL_TXT,10,15)
   FROM PAC, RCL, SMK, HSP,LOC,STR
   WHERE ( RCL.RCL_PAC = PAC.PAC_REG  ) AND
   ( RCL.RCL_TPCOD = SMK.SMK_TIPO ) AND 
   ( RCL.RCL_COD = SMK.SMK_COD )  AND 
   ( SMK.SMK_CTF = 10141 ) AND
   ( RCL.RCL_PAC = HSP.HSP_PAC) AND
   ( HSP_STAT = 'A') AND
   ( HSP.HSP_LOC = LOC.LOC_COD) AND
   ( STR.STR_COD = LOC.LOC_STR) AND 
   ( STR.STR_COD = :POSTO ) AND
   ( RCL.RCL_STAT NOT IN ('C','A')) AND
   ( DATEDIFF(HSP.HSP_DTHRE, now()) > 2) 
   GROUP BY  RCL.RCL_PAC,PAC.PAC_NOME,SMK.SMK_ROT,HSP.HSP_DTHRE,HSP.HSP_DTHRA,HSP.HSP_STAT,HSP.HSP_NUM,HSP.HSP_pac,HSP.HSP_LOC,LOC.LOC_NOME,LOC.LOC_STR,STR.str_nome,RCL.RCL_STAT,substr(RCL_TXT,10,15)

Answer 1

With the select below you will be able to bring all the data (columns), only of the most recent records of each patient:

Sqlfiddle - Online example:

SELECT Tabela.Reg
  , Tabela.Paciente
  , Tabela.Titulo
  , Tabela.Data
FROM Tabela
  JOIN (
    SELECT Paciente
    , MAX(Data) UltimaData
    FROM Tabela
    GROUP BY Paciente
  ) UltimoRegistro
ON Tabela.Data = UltimoRegistro.UltimaData
AND Tabela.Paciente = UltimoRegistro.Paciente

Answer 2

I built a generic query using GROUP BY to group REG (including also PATIENT for easier reading) and MAX for the date:

select REG, PACIENTE, max(DATA)
  from Tabela
 group by REG, PACIENTE

Here a working example: http://sqlfiddle.com/#! 18/117a4

As @Rovannlinhalis commented, a primary key would help here, because if you want to know the TITLE would need a key, a ID for example, if it includes TITLE in the GROUP BY, will group in the wrong way.