CODEWITHSUNDEEP
oracle-database
Tahirsatti123
Tahirsatti123

Reputation: 9

How to compare last record value with all prvious records in oracle sql select query

I have oracle table having values just like given below.

f_name            f_size                f_date
abc.csv           100                   2016-08-01 10:55:55
abc.csv           200                   2016-08-01 11:55:55
abc.csv           300                   2016-08-01 12:55:55
abc.csv           400                   2016-08-01 14:55:55

My requirement is that i want to compare last value with all previous values on the basis of f_size and f_date and if last record f_size is greater then all previous value(f_size value) and last f_date is maximum i.e max(date) then all previous dates then one record will be in output and in above case following will be output.

abc.csv           400                   2016-08-01 14:55:55

Upvotes: 0

Views: 146

Answers (1)

user5683823
user5683823

Reputation:

You probably want something like this. Note - I added more test data (added another f_name where the condition is not met) edit: and another case where the max file size is not unique.

This assumes you need the query to work separately for each f_name, if instead it should work the same across all file names just change partition by f_name to partition by null.

Edit: solution modified to exclude cases when the file with max size and max date has same size as other versions of the file.

with
     test_data ( f_name, f_size, f_date ) as (
       select 'abc.csv', 100, to_date('2016-08-01 10:55:55', 'yyyy-mm-dd hh24:mi:ss') from dual union all
       select 'abc.csv', 200, to_date('2016-08-01 11:55:55', 'yyyy-mm-dd hh24:mi:ss') from dual union all
       select 'abc.csv', 300, to_date('2016-08-01 12:55:55', 'yyyy-mm-dd hh24:mi:ss') from dual union all
       select 'abc.csv', 400, to_date('2016-08-01 14:55:55', 'yyyy-mm-dd hh24:mi:ss') from dual union all
       select 'efg.txt', 200, to_date('2016-08-12 04:30:00', 'yyyy-mm-dd hh24:mi:ss') from dual union all
       select 'efg.txt', 100, to_date('2016-08-12 05:00:00', 'yyyy-mm-dd hh24:mi:ss') from dual union all
       select 'hij.log', 800, to_date('2016-08-16 05:05:00', 'yyyy-mm-dd hh24:mi:ss') from dual union all
       select 'hij.log', 100, to_date('2016-08-16 05:30:00', 'yyyy-mm-dd hh24:mi:ss') from dual union all
       select 'hij.log', 800, to_date('2016-08-12 05:00:00', 'yyyy-mm-dd hh24:mi:ss') from dual
     ),
-- end of test data; solution (SQL query) begins below this line 
-- (add the keyword WITH to the beginning of the query below)
     prep ( f_name, f_size, f_date, max_size, max_date ) as  (
       select f_name, f_size, f_date, 
              max(f_size) over (partition by f_name),
              max(f_date) over (partition by f_name)
       from   test_data
     )
select f_name, f_size, f_date
from   prep
where f_size = max_size and f_date = max_date
  and f_name in ( select   f_name 
                  from     prep 
                  where    f_size = max_size 
                  group by f_name 
                  having   count(*) = 1
                )
;  

F_NAME   F_SIZE   F_DATE
-------  ------   -------------------
abc.csv     400   2016-08-01 14:55:55

Upvotes: 1

Related Questions