Always bring the last result

[Dualogic]

Good morning,

I'm starting to work with SQL and I'm having a problem I need to always bring the last record of a table in the following SQL



I need to bring only and always the first record of this table in bold

[spernega]

Good afternoon,

in the UOP_CID_PACIENT table Do you have any field that can be used as a parameter?

For example:

Select all

And d.dt_incl = (Select max(dt_incl)  
from uop_cid_paciente  
where cd_pessoa_fisica = d.cd_pessoa_fisica)

Is it more or less what you want?

[Dualogic]

this, just that,
I just did not understand that part:

Select all

d.dt_incl

Which parameter should I pass?

[spernega]

Hi,

In the example, if your UOP_CID_PACIENT table has more than one record for each patient and you want to always bring the last record of it, you could filter by the date of inclusion of the registry.
SubQuery will bring the last record released based on the inclusion date or another single field that exists in the table.

That is, you will search only the consultations for the last patient's entry.

Now a more "strange" answer:
If you just want the Select to return only one line, you can include another clause A at Query:


- To bring the prime record that you can find:

Select all

where rownum <= 1 
and   dt_prevista  between  :dt_inicial and fim_dia(:dt_final) 
and   d.cd_pessoa_fisica = c.cd_pessoa_fisica 
and   exists (select 1  
              from paciente_setor b  
              where b.nr_seq_paciente = a.nr_seq_paciente  
              and b.ie_status = 'A')                         
and   a.dt_cancelamento is null  
and   a.cd_estabelecimento = :cd_estabelecimento 
and   a.nr_atendimento = c.nr_atendimento 
order by nm_paciente

To bring the last record you would put an order by Field desc

But I guess that is not what you need.

In any case, if you give, explain more in detail what you need.

[Dualogic]

Hello,

I gave an improved and a "wiped" in my code and was like this,

Select all

select   substr(obter_idade_pf(c.cd_pessoa_fisica, sysdate, 'A'),1,100)qt_idade,    
            substr(obter_nome_paciente_setor(a.nr_seq_paciente),1,60) nm_paciente,  
            substr(obter_nome_medico_setor(a.nr_seq_paciente),1,60) nm_medico,  
            substr(obter_desc_prot_medic(a.nr_seq_paciente),1,255) ds_protoc,                   
            substr(obter_desc_cid(d.CD_CID),1,200)ds_doenca, 
            a.qt_peso              
from     paciente_atendimento a,  
            atendimento_paciente c, 
            uop_cid_paciente d 
where   a.dt_prevista  between  :dt_inicial and fim_dia (:dt_final) 
and      d.dt_diagnostico = (select max(dt_diagnostico) from uop_cid_paciente where d.cd_pessoa_fisica = c.cd_pessoa_fisica)                 
and      a.dt_cancelamento is null  
and      a.cd_estabelecimento = :cd_estabelecimento 
and      a.nr_atendimento = c.nr_atendimento 
and      d.cd_pessoa_fisica = c.cd_pessoa_fisica 
 
 
order by     nm_paciente

but even informing The parameters the query comes blank, and I do not know what else to do to solve this problem

[spernega]

How can you generate a script with the creation of the tables and the loads?
So I try to reproduce here.

[Dualogic]

Do you want a script from my bank?

[spernega]

Only the tables involved in Query.
with the inserts, if not too big +

[Dualogic]

I'm going back, I'm starting with SQL and I know little of the tools for this, how do I generate this script in SQL Navigator 6.1

[spernega]

I do not use SQL Navigator, I'm from the old guard, I use only SQLPlus

must have options to generate DDL of tables and inserts, at least in SQLDeveloper has.

[Dualogic]

One of the tables this query has 136008 records

[spernega]

It's a lot,

if you can, just send the same scripts

[geovani]

Oops!

Try as follows:

Select all

select 
   substr(obter_idade_pf(c.cd_pessoa_fisica,sysdate,'A'),1,100) qt_idade, 
   substr(obter_nome_paciente_setor(a.nr_seq_paciente),1,60) nm_paciente, 
   substr(obter_nome_medico_setor(a.nr_seq_paciente),1,60) nm_medico, 
   substr(obter_desc_prot_medic(a.nr_seq_paciente),1,255) ds_protoc, 
   substr(obter_desc_cid(d.CD_CID),1,200)mds_doenca, 
   a.qt_peso   
from 
   paciente_atendimento a, 
   atendimento_paciente c, 
   uop_cid_paciente d   
where 
   a.dt_prevista between :dt_inicial and fim_dia (:dt_final)   
   and d.dt_diagnostico = ( 
      select 
         max(ultimo.dt_diagnostico)  
      from 
         uop_cid_paciente ultimo 
      where 
         d.cd_pessoa_fisica = ultimo.cd_pessoa_fisica 
   )   
   and a.dt_cancelamento is null   
   and a.cd_estabelecimento = :cd_estabelecimento   
   and a.nr_atendimento = c.nr_atendimento   
   and d.cd_pessoa_fisica = c.cd_pessoa_fisica