0

I had a table named uniq_select_records with the following columns:

sno number, 
item_name varchar2(50),
start_date date, 
end_date date, 
action_flag char(1),
last_modified_date date, 
creation_date date

sno is a column which I used to identify a row for a item_name and it is not unique or primary key but it was not null. sno will be present for each item. no of items is equal to no of sno. sample

sno item_name start_date  end_date Action_flag last_modified creation_date
--- --------- ----------  -------- ----------- ------------- -------------
1   sample1   02-MAY-15  27-MAY-14    A        07-MAY-15        02-MAY-15
1   sample1   02-MAY-15  27-MAY-14    D        07-MAY-15        02-MAY-15
1   sample1   27-APR-15  06-JUN-14    C        07-MAY-15        02-MAY-15
1   sample1   27-APR-15  06-JUN-14    C        07-MAY-15        03-MAY-15
2   sample2   07-MAY-15  11-FEB-15    C        07-MAY-15        22-JAN-15
2   sample2   07-MAY-15  11-FEB-15    A        07-MAY-15        22-JAN-15
2   sample2   07-MAY-15  01-FEB-15    C        12-MAY-15        12-MAY-15

Action flag values C=change, D='Deleted and A=insert sample data is shown above. like this we have millions of records. now we have to select the to record which had the latest change for each item. each item may have its own start_date and end_date.All the items weren't modified on the same date.

Can anyone suggest idea how to fetch the records for each item which are latest change for an item.

thanks in advance.

  • as marked this question is not similar to the above link –  May 22 '15 at 09:59
  • 1
    dear friend the the following query `select distinct sno,item_name,max(start_date) over(partition by sno),max(end_date) over(partition by sno),max(creation_date) over(partition by sno), max(last_modified_date) over(partition by sno) from uniq_select_records order by sno,item_name asc;` – Smart003 Jun 17 '15 at 10:00
  • 1
    @Smart003 thanks its working, the query gives an accurate result which i was expecting –  Jun 17 '15 at 10:02

1 Answers1

1
SELECT sno, item_name, max(last_modified)
  FROM uniq_select_records
GROUP BY sno, item_name
davegreen100
  • 2,055
  • 3
  • 13
  • 24