Multiple date sorting query

if you want to sort data, then
you can say

select number, dt1, dt2, dt3, dt4, flagkeep
from your_table
order by number, dt4 desc -- similary you can add here other columns which are reqired.

Just note that we are not filtering any records here. ( i mean we are not excluding any records because we are not using any where clause in the select statement ).

I did not understand much of your explanation given above. If you put some sample data and then say which one you want and which one you do not want, then we can add where clauses accordingly.

Thanks

can you try the query below and see if it correctly identifies the ones you want to keep? if it does, change it to an UPDATE

select rank() over (
 partition by n, dt4
 order by
  nvl(dt3, '0000') desc,
  nvl(dt2, '9999') desc,
  nvl(dt1, '9999') desc
) as ranked, n, dt1, dt2, dt3, dt4
from table1 a
where ranked = 1 and not exists
(
 select 1 from table1 b
 where a.n = b.n
   and a.dt4 < b.dt4
   and a.dt1 >= b.dt1
)

Select allOpen in new window

ps
>>If they are i need to keep the records where the dt3 is not null.
>>If there is no dt3 i need to keep the one with the greatest d2 (null is greater).
>>If all dt3 are the same then i need to keep the one where dt1 is greater.

how does that explain the selection

1 0501 0630 null 0420 Y
1 0501 null null 0420 N    << shouldn't it be this one?

1, dt3 is null - rule 1 doesn't apply
2, dt3, keep greatest d2 - null is greater, therefore 2nd row

no?

also, with #3, do you mean all DT3 or all DT2? I assume you mistyped and it should be DT2

1, dt3 is null - rule 1 doesn't apply
2, dt3, keep greatest d2 - null is greater, therefore 2nd row

no?

also, with #3, do you mean all DT3 or all DT2? I assume you mistyped and it should be DT2
You are right it should be the second line !

For #3 it is if all DT2 are the same then i keep the greatest DT1

have you tried my query above? it should give you what you want

Hi i tried and it had to change a little
to this


select no_tel, dt_deb, dt_fin, dt_com, dt_eff_inet from
(select rank() over ( partition by no_tel, dt_eff_inet
order by
    nvl (dt_com, to_date(19000101,'yyyymmdd')) desc,
      nvl (dt_fin, to_date(20090101,'yyyymmdd')) desc,
      nvl (dt_deb, to_date(20090101,'yyyymmdd')) desc)
      r, no_tel, dt_deb, dt_fin, dt_com, dt_eff_inet
      from cleaned_sga_tpc  ) a where r = 1 and not exists
      ( select 1 from cleaned_sga_tpc b where a.no_tel = b.no_tel and a. dt_eff_inet < b.dt_eff_inet and a.dt_deb >= b.dt_deb)

however i don't get why it doesn't give me 2 lines (is it because r needs to be =1 ) ?
I really need the first of each greater dt4 (dt_eff_inet in real) unless the dt1 (dt_deb in real) remains the same
here is some live data
2007-02-01  2007-10-09  null 2007-11-19
2007-10-12 2007-10-11  null 2007-11-19
2007-10-13 2007-10-17 null 2007-11-19
2007-10-13 2007-10-17  null 2007-10-16
2007-10-13  null              null 2007-10-15
2007-02-01 null              null  2007-07-24
2007-02-01  null             null  2007-07-20

It returns the 3rd line which is ok.. but why isn't it returning the 6th line also

Thanks for the help :)

i thought you meant that dates are stored in char(4) implicitly year 2007?

Thanks for all the help, unfortunatelly i wasn't logged on anymore at 2:22 EST !
I'll check it out and get back to you as soon as i can.
I can have date throughout 2004 to present (and even some dates in the near future) it was juste easier to fit the format in one line in this template so i just took examples all in the same year for practical purposes ;)


Thanks again a million times... i wish i could help out as you do !

A must have expert on your team... Superb and accurate way to fetch the results needed