Click here to Skip to main content
15,917,612 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
i had a table name uniq_select_records with columns
C#
(sno number, 
item_name varchar2(50),
start_date date, 
end_date date, 
action_flag char(1),
last_modified_date date, 
creation_date date)


sno i 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

C#
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

C#
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 not have the same 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.

expected output is
XML
sno   item_name start_date      end_date   Action_flag last_modified creation_date
---  ---------- ---------       ---------- ----------- ------------- -------------
1   sample1 27-APR-15   06-JUN-14   C   07-MAY-15   03-MAY-15
2   sample2 07-MAY-15   01-FEB-15   C   12-MAY-15   12-MAY-15

thanks in advance....
Posted
Updated 8-Jun-15 1:24am
v4
Comments
Praveen Kumar Upadhyay 22-May-15 7:11am    
So you want the unique record based on last_modified?
Smart003 22-May-15 8:48am    
yes Praveen i want to list the records based on the last_modified
Mathi Mani 22-May-15 12:46pm    
What did you try? Can you share the query you have?
Smart003 25-May-15 7:41am    
select * from uniq_select_records where last_modified in (select max(last_modified) from uniq_select_records);
Thava Rajan 25-May-15 7:56am    
what is the output you want to show from the query

1 solution

SQL
WITH last_mod AS (
    SELECT
            sno
           ,Max(last_modified_date) last_modified_date
    FROM    uniq_select_records
    GROUP BY sno
    )
SELECT
        u.sno
       ,u.item_name
       ,u.start_date
       ,u.end_date
       ,u.Action_flag
       ,u.last_modified_date
       ,u.creation_date
FROM    uniq_select_records u
JOIN    last_mod l
    ON  u.sno = l.sno
    AND u.last_modified_date = l.last_modified_date)
 
Share this answer
 
Comments
Maciej Los 25-May-15 17:22pm    
Looks promisingly ;) +5!
I'd add one note: Oracle supports CTE starting from 9.2 version ;) Source: WITH Clause : Subquery Factoring
Jörgen Andersson 26-May-15 1:48am    
Yeah, I know, but 9i was released in 2002, so I'm frankly expecting people to have moved on.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900