Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Shahir Deo
Ranch Hand
Posts: 80
Hibernate Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
K. Tsang
Bartender
Posts: 3398
13
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can try selecting the max(start date) for each ID
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic