How to get the latest (or earliest) records?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: How to get the latest (or earliest) records?

  1. #1
    Join Date
    Jul 2007
    Posts
    7

    How to get the latest (or earliest) records?

    Hi

    I have a history table (in oracle) with following columns..
    split_id | policy | mod_dt | chng_cd
    to maintain any changes to the policies

    and I have a requirement to get all the latest changes for each policies. Please suggest an easy method to get the latest records for each policlies without using stored procedure or pl/sql.
    The split_id field here is not unique. For eg:
    split_id | policy | mod_dt | chng_cd
    111111 |ABC11 |6/9/2006 11:40:16 AM | U
    111111 |ABC11 |6/9/2006 11:40:16 AM | D
    111112 |ABC12 |4/9/2002 01:12:11 AM | A
    111112 |ABC12 |2/7/2005 07:10:31 AM | U
    111112 |ABC12 |1/4/2007 10:22:19 AM | E

    Please help!

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    How do you expect to retrieve the data from an Oracle table w/o using PL/SQL?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Jul 2007
    Posts
    7
    Thanks for the reply Paul, am I expecting too much here? Can't I have an SQL statement to do this in Oracle 8i+ instead of PL/SQL (I mean procedure/function)? I am not sure but can I use RANK() and PARTITION?

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    Maybe I'm missing something here but RANK is a PL/SQL function. Are you using a tool or utility to retrieve this information or is there a programming language involved?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  5. #5
    Join Date
    Jul 2007
    Posts
    7
    May be I am not able to use the right terminology here, so I would like to reframe my problem... Actually I was reluctant to create an oracle stored procedure to do this task so I thought if someone can help me in writting a simple SQL query or subquery to accomplish this. Contrary to this, if you have a better and simple solution then please let me know. I am not using any other language/utility but need to get the data in SQLPlus/TOAD. Sorry for the confusion..

  6. #6
    Join Date
    Dec 2003
    Posts
    2,750
    Off the top of my head see if the following works for you:

    Code:
    SELECT HT1.SPLIT_ID, HT1.POLICY, HT1.MOD_DT, HT1.CHNG_CD FROM HISTORY_TABLE HT1,
    	(SELECT SPLIT_ID, POLICY, MAX(MOD_DT) AS MOD_DT, CHNG_CD FROM HISTORY_TABLE
    		GROUP BY POLICY) HT2
    	WHERE HT1.SPLIT_ID = HT2.SPLIT_ID AND
    	      HT1.POLICY = HT2.POLICY AND
    	      HT1.MOD_DT = HT2.MOD_DT AND
    	      HT1.CHNG_CD = HT2.CHNG_CD
    	ORDER BY SPLIT_ID
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  7. #7
    Join Date
    Jul 2007
    Posts
    2

    latest record

    Hi ,
    try this one

    select split_id , policy, max(mod_dt)
    from history_table
    group by split_id , policy

    this will retrun like
    111111 ABC11 2006-06-09 11:40:16.000
    111112 ABC12 2007-01-04 10:22:19.000

    Thanks
    DG

Similar Threads

  1. Replies: 0
    Last Post: 06-28-2007, 09:55 AM
  2. Replies: 0
    Last Post: 07-19-2002, 01:41 AM
  3. saving records in Access
    By Hian Chew in forum VB Classic
    Replies: 1
    Last Post: 03-28-2001, 12:45 PM
  4. savomg records in Access
    By Hian Chew in forum VB Classic
    Replies: 0
    Last Post: 03-28-2001, 10:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center