How to get a list without duplicate records?

I need to get a list without some duplicate records value. Please look into the queried list for details.

The first list is queried from original table, the second list is a middle result with adding two functions, the third list is the expect result.

Please help get the third list result, any body can help will be great appreciate.

By the way, the hard part is need keep the plsh.plsh_id in the final result, so can not using distinct at second list. The current_smc_value and previous_smc_value need keep continues.

The original data is like below:

select plsh.plsh_id,
       plsh.patr_id,
       plsh.smc         CURRENT_SMC,
       plsh.create_date CURRENT_SMC_DATE
  from pat_liver_smc_his plsh
 where plsh.patr_id = 34461
 order by plsh.plsh_id desc;

and the result is

    PLSH_ID PATR_ID CURRENT_SMC CURRENT_SMC_DATE
1   10020   34461       22      2/19/2013 1:23:58 PM
2   10019   34461       22      2/19/2013 1:22:57 PM
3   10018   34461       27      2/19/2013 1:22:42 PM
4   10017   34461       27      2/19/2013 1:22:42 PM
5   10016   34461       27      2/19/2013 1:22:32 PM
6   10015   34461       27      2/19/2013 1:22:32 PM
7   10014   34461       27      2/19/2013 1:22:20 PM
8   10013   34461       27      2/19/2013 1:22:20 PM
9   10012   34461       27      2/19/2013 1:22:09 PM
10  10011   34461       27      2/19/2013 1:21:50 PM
11  10010   34461       24      2/19/2013 1:21:36 PM
12  10009   34461       22      2/19/2013 1:21:06 PM
13  10008   34461               2/19/2013 1:21:06 PM

The requirement need get previous_smc, previous_smc_date and trunc date, so I create below query statement and two functions.

select plsh.plsh_id,
       plsh.patr_id,
       tttt_gen_pkg.get_previous_SMC(plsh.patr_id, plsh.plsh_id) PREVIOUS_SMC,
       trunc(tttt_gen_pkg.get_previous_SMC_Date(plsh.patr_id, plsh.plsh_id)) PREVIOUS_SMC_DATE,
       plsh.smc CURRENT_SMC,
       trunc(plsh.create_date) CURRENT_SMC_DATE
  from pat_liver_smc_his plsh
 where plsh.patr_id = 34461
 order by plsh.plsh_id desc;

function get_previous_SMC(p_patr_id varchar2, p_plsh_id varchar2)
  return number is
  cursor c_smc is
    select plsh.smc
      from pat_liver_smc_his plsh
     where plsh.patr_id = p_patr_id
       and plsh.plsh_id < p_plsh_id
     order by plsh.plsh_id desc;
  v_rst number;
begin
  open c_smc;
  fetch c_smc
    into v_rst;
  close c_smc;
  return v_rst;
end;

function get_previous_SMC_Date(p_patr_id varchar2, p_plsh_id varchar2)
  return date is
  cursor c_smc is
    select plsh.create_date
      from pat_liver_smc_his plsh
     where plsh.patr_id = p_patr_id
       and plsh.plsh_id < p_plsh_id
     order by plsh.plsh_id desc;
  v_rst date;
begin
  open c_smc;
  fetch c_smc
    into v_rst;
  close c_smc;
  return v_rst;
end;

and the result is

    PLSH_ID PATR_ID PREVIOUS_SMC    PREVIOUS_SMC_DATE   CURRENT_SMC     CURRENT_SMC_DATE
1   10020   34461       22          2/19/2013               22          2/19/2013
2   10019   34461       27          2/19/2013               22          2/19/2013
3   10018   34461       27          2/19/2013               27          2/19/2013
4   10017   34461       27          2/19/2013               27          2/19/2013
5   10016   34461       27          2/19/2013               27          2/19/2013
6   10015   34461       27          2/19/2013               27          2/19/2013
7   10014   34461       27          2/19/2013               27          2/19/2013
8   10013   34461       27          2/19/2013               27          2/19/2013
9   10012   34461       27          2/19/2013               27          2/19/2013
10  10011   34461       24          2/19/2013               27          2/19/2013
11  10010   34461       22          2/19/2013               24          2/19/2013
12  10009   34461                   2/19/2013               22          2/19/2013
13  10008   34461                                                       2/19/2013

The expect final result is like below: How to get it? The PLSH_ID is required in final result so distinct to above query statement is not working.

    PLSH_ID PATR_ID PREVIOUS_SMC    PREVIOUS_SMC_DATE   CURRENT_SMC     CURRENT_SMC_DATE
1   10020   34461       22          2/19/2013               22          2/19/2013
2   10019   34461       27          2/19/2013               22          2/19/2013
3   10018   34461       27          2/19/2013               27          2/19/2013
10  10011   34461       24          2/19/2013               27          2/19/2013
11  10010   34461       22          2/19/2013               24          2/19/2013
12  10009   34461                   2/19/2013               22          2/19/2013
13  10008   34461                                                       2/19/2013

Hi Egor,

Thank you very much for your effort. After a little bit modify your code I got below result,
it seems current_smc and current_smc_date order is correct but the previous_smc and previous_smc_date still not correct,
the result is like below:

    PLSH_ID PATR_ID PREVIOUS_SMC    PREVIOUS_SMC_DATE   CURRENT_SMC     CURRENT_SMC_DATE
1   10020   34461                                       22              2/19/2013
2   10019   34461   22              2/19/2013           22              2/19/2013
3   10018   34461   22              2/19/2013           27              2/19/2013
4   10017   34461   27              2/19/2013           27              2/19/2013
5   10010   34461   27              2/19/2013           24              2/19/2013
6   10009   34461   24              2/19/2013           22              2/19/2013
7   10008   34461   22              2/19/2013                           2/19/2013

A little bit modify of your code is like below:

select plsh_id,
       patr_id,
       PREVIOUS_SMC,
       PREVIOUS_SMC_DATE,
       CURRENT_SMC,
       CURRENT_SMC_DATE
  from (select plsh_id,
               patr_id,
               CURRENT_SMC,
               CURRENT_SMC_DATE,
               PREVIOUS_SMC,
               PREVIOUS_SMC_DATE,
               case
                 when decode(CURRENT_SMC,
                             lag(CURRENT_SMC) over(partition by t1.patr_id
                                  order by t1.plsh_id desc),
                             1) = 1 and decode(CURRENT_SMC_DATE,
                                               lag(CURRENT_SMC_DATE)
                                               over(partition by t1.patr_id
                                                    order by t1.plsh_id desc),
                                               1) = 1 and
                      decode(PREVIOUS_SMC,
                             lag(PREVIOUS_SMC)
                             over(partition by t1.patr_id order by
                                  t1.plsh_id desc),
                             1) = 1 and
                      decode(CURRENT_SMC,
                             lag(CURRENT_SMC) over(partition by t1.patr_id
                                  order by t1.plsh_id desc),
                             1) = 1 then
                  1
               end as the_same
          from (select plsh.plsh_id,
                       plsh.patr_id,
                       trunc(plsh.smc) CURRENT_SMC,
                       trunc(plsh.create_date) CURRENT_SMC_DATE,
                       lead(trunc(plsh.smc)) over(partition by plsh.patr_id order by plsh.plsh_id) PREVIOUS_SMC,
                       lead(trunc(plsh.create_date)) over(partition by plsh.patr_id order by plsh.plsh_id) PREVIOUS_SMC_DATE
                  from pat_liver_smc_his plsh
                 where plsh.patr_id = 34461) t1)
 where the_same is null
 order by patr_id, plsh_id desc;

Can you please help me to correct it? Some of you code I don't understand because you are more senior level than me. Thanks again!!!

The final perfect work version is from DazzaL. The modified working code is below:

select plsh_id,
       patr_id,
       previous_smc,
       previous_smc_date,
       current_smc,
       current_smc_date
  from (select plsh_id,
               patr_id,
               previous_smc,
               previous_smc_date,
               current_smc,
               current_smc_date,
               row_number() over(partition by patr_id, current_smc, previous_smc order by plsh_id desc) rn
          from (select plsh_id,
                       patr_id,
                       plsh.smc current_smc,
                       plsh.create_date current_smc_date,
                       lag(plsh.smc) over(partition by patr_id order by plsh_id) previous_smc,
                       lag(plsh.create_date) over(partition by patr_id order by plsh_id) previous_smc_date
                  from pat_liver_smc_his plsh))
 where rn = 1 and patr_id = 34461
 order by patr_id, plsh_id desc

Upvotes: 0

Answers (2)

DazzaL

Reputation: 21973

something like;

select plsh_id, patr_id, previous_smc, previous_smc_date, 
       current_smc, current_smc_date
  from (select plsh_id, patr_id, previous_smc, 
               previous_smc_date, current_smc,
               current_smc_date,
               row_number() over(partition by patr_id, current_smc, 
                                 current_smc_date, previous_smc, 
                                 previous_smc_date  
                                 order by plsh_id desc) rn          
          from (select plsh_id, patr_id, current_smc,
                       trunc(current_smc_date) current_smc_date,
                       lag(current_smc) over(partition by patr_id 
                                             order by plsh_id) previous_smc,
                       trunc(lag(current_smc_date)
                               over(partition by patr_id
                                    order by plsh_id)) previous_smc_date
                  from pat_liver_smc_his))
 where rn = 1
 order by patr_id, plsh_id desc

Upvotes: 1

Egor Skriptunoff

Reputation: 23747

select plsh_id,
       patr_id,
       CURRENT_SMC,
       CURRENT_SMC_DATE,
       PREVIOUS_SMC,
       PREVIOUS_SMC_DATE
from (
    select plsh_id,
           patr_id,
           CURRENT_SMC,
           CURRENT_SMC_DATE,
           PREVIOUS_SMC,
           PREVIOUS_SMC_DATE,
           case when 
             decode(CURRENT_SMC, lag(CURRENT_SMC) over (partition by plsh.patr_id order by plsh.plsh_id desc), 1) = 1
             and   
             decode(CURRENT_SMC_DATE, lag(CURRENT_SMC_DATE) over (partition by plsh.patr_id order by plsh.plsh_id desc), 1) = 1
             and   
             decode(PREVIOUS_SMC, lag(PREVIOUS_SMC) over (partition by plsh.patr_id order by plsh.plsh_id desc), 1) = 1
             and   
             decode(CURRENT_SMC, lag(CURRENT_SMC) over (partition by plsh.patr_id order by plsh.plsh_id desc), 1) = 1
             then 1
           end as the_same
    from (
      select 
           plsh.plsh_id,
           plsh.patr_id,
           trunc(plsh.smc)         CURRENT_SMC,
           trunc(plsh.create_date) CURRENT_SMC_DATE,
           lead(trunc(plsh.smc)) over (partition by plsh.patr_id order by plsh.plsh_id) PREVIOUS_SMC,
           lead(trunc(plsh.create_date)) over (partition by plsh.patr_id order by plsh.plsh_id) PREVIOUS_SMC_DATE
      from pat_liver_smc_his plsh
      where plsh.patr_id = 34461
    )
)
where the_same is null
order by plsh.patr_id, plsh.plsh_id desc;

Upvotes: 1

How to get a list without duplicate records?

Answers (2)

Related Questions