How to keep only the recent date in sql and delete the rest from the table based on conditions?-CodePudding

I have a SQL table and my data looks like this:

PAN_NO |NIFTY_TREND | COUNT_OF_TREND | PURCHASE_DATE | NEW_SCH_CODE

XXX    |    011     |      1         |   29-SEP-16   |    168
YYY    |    111     |      1         |   02-JUN-17   |    168
ZZZ    |    011     |      3         |   27-OCT-14   |    178
ZZZ    |    011     |      3         |   28-JUN-17   |    168
ZZZ    |    011     |      3         |   28-JUN-17   |     04

Condition for expected output:

For each pan if the count_of_trend is greater than 1 then i should keep the record which has the recent purchase date and remove the rest. If the recent purchase date is also the same anyone among the 2 can be selected.

Expected Output :

PAN_NO |NIFTY_TREND | COUNT_OF_TREND | PURCHASE_DATE | NEW_SCH_CODE

XXX    |    011     |      1         |   29-SEP-16   |    168
YYY    |    111     |      1         |   02-JUN-17   |    168
ZZZ    |    011     |      3         |   28-JUN-17   |    168

I tried doing something like this:

select pan_no, nifty_trend, count_of_trend, max(purchase_date) over (partition by pan_no)
from table group by pan_no having count_of_trend >1

but the above code doesnt work.

I am performing all of this in sql developer.

CodePudding user response：

To me, it looks like this:

Sample data:

SQL> with test (pan_no, nifty_trent, count_of_trend, purchase_date, new_sch_code) as
  2    (select 'xxx', '011', 1, date '2016-09-29', 168 from dual union all
  3     select 'yyy', '111', 1, date '2017-06-02', 168 from dual union all
  4     select 'zzz', '011', 3, date '2014-10-27', 178 from dual union all
  5     select 'zzz', '011', 3, date '2017-06-28', 168 from dual union all
  6     select 'zzz', '011', 3, date '2017-06-28',   4 from dual
  7    ),

Query begins here; temp CTE "sorts" rows for each pan_no by purchase_date in descending order; the final select then returns either rows whose rn = 1 (i.e. they rank as the "highest"), or count_of_trend = 1 (because sorting has to be applied only to rows whose count_of_trend is larger than 1).

  8  temp as
  9    (select t.*,
 10       row_number() over (partition by pan_no order by purchase_date desc) rn
 11     from test t
 12    )
 13  select pan_no, nifty_trent, count_of_trend, purchase_date, new_sch_code
 14  from temp
 15  where rn = 1
 16     or count_of_trend = 1;

PAN NIF COUNT_OF_TREND PURCHASE_D NEW_SCH_CODE
--- --- -------------- ---------- ------------
xxx 011              1 29.09.2016          168
yyy 111              1 02.06.2017          168
zzz 011              3 28.06.2017            4

SQL>