aspose file tools*
The moose likes Oracle/OAS and the fly likes Oracle - Retriving only latest data from a table if there is a Duplicate. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Oracle - Retriving only latest data from a table if there is a Duplicate." Watch "Oracle - Retriving only latest data from a table if there is a Duplicate." New topic
Author

Oracle - Retriving only latest data from a table if there is a Duplicate.

Shahir Deo
Ranch Hand

Joined: Mar 19, 2013
Posts: 80

Hi Guys,

We are writing one select query to display the data in the table for Report purpose in GUI.

The Problem is In the Report we have duplicates (older data),

Ex:

Sr.Value----------------Start Date------------End Date-------------ID-------------SOME_PRIMARY_KEY
1. XYZ(01) -------------21-01-13-------------21-01-14-------------01-------------4596
2. XYZ(07) -------------11-01-13-------------11-01-14-------------07-------------4589
3. XYZ(01) -------------21-01-12-------------21-01-13-------------01-------------4567
4. XYZ(05) -------------05-01-11-------------21-06-11-------------05-------------4512

After the Insertion of new record in table sr .1
it is displaying All the 1 , 2, 3, 4 .

But i need to eliminate or do not want to show the old ID :01 (sr.3) as the new ID:01 is inserted in Table.

Please Help.

Thanks
This message was edited 1 time. Last update was at Today 12:09:53 PM by Shahir Deo

Knowledge enlivens the soul.
K. Tsang
Bartender

Joined: Sep 13, 2007
Posts: 2596
    
    9

You can try selecting the max(start date) for each ID


K. Tsang JavaRanch SCJP5 SCJD/OCM-JD OCPJP7 OCPWCD5 OCPBCD5
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

I assume the start_date column corresponds to the "age" of the record, but the queries would be easy to change to use the end_date. I also assume that the combination of Id and start_date is unique in the table (otherwise you could have two or more records for the same id with the dame date, and we couldn't distinguish between them).

There are several possibilities. Firstly, you could use a subquery which would select id and max(start_date) grouping by id from the table, and the outer query would select all records for whose the id and start_date appear in the subquery. Something like this:
The thing to note here is that you can use more than one column with the IN operator (just put the list of columns into parentheses).


Second option would be to use analytic function to compute the rank of each record and only select records with rank equal to 1. We need to use subquery again though, because analytic functions cannot appear in the where clause:
Note that we sort the records by age in descending order, because we want the newest record (the one with the highest date) to have a rank of 1.

Analytic functions are immensely powerful. The full documentation is here, but if you find the text to be too technical, you should be able to google lots of examples of using individual functions, eg. oracle rank function.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle - Retriving only latest data from a table if there is a Duplicate.