Select last record from a table - sql

Question

Select last record from a table - sql

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Good afternoon! I have a table with multiple records for multiple 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 the evolution of the day 02/02/2018

I'm using max (date) but in the result comes the last evolution and the last admission of each patient, as if the max date was working for each TYPE of record and not per patient.

I'm using MAXDB

My select was as follows, in fact I'm putting together some tables, and I had to group all the items I'm 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)

sql

asked by anonymous 18.04.2018 / 20:41

2 answers

Error in pom.xml Gem Wicked_pdf - page break repeats the first row of the table

score 0 · Answer 1

I built a generic query, using GROUP BY to group REG (also includes PATIENT for easy reading) and MAX for DATA:

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

Here's an example working: link

As @RovannLinhalis commented, a primary key would help here, because if you want to know the TITLE you would need a key, a ID eg to include TITLE < strong> no GROUP BY , will group wrongly.

score 0 · Answer 2

With the select below you will be able to bring all the data (columns), only 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