How to get the latest Record — oracle-tech

    Forum Stats

  • 3,716,003 Users
  • 2,242,928 Discussions
  • 7,845,734 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

How to get the latest Record

User_CC24U
User_CC24U Member Posts: 6 Green Ribbon
edited February 19 in SQL & PL/SQL

Hi Gurus,

I have a need to pull the history data from an employee table where the user status is terminated. The sample inserts are attached.

The output I need as below. The least record of an employee with status terminated, but if there are multiple recent terminations like 1134166 (3 terminated records), need to fetch the 3 record.


  • 1134166 Aayushi Bhatnagar  Terminated 22-JAN-21 15.07.15 22-JAN-21 15.08.07
  • 1172439 Michael Tam      Terminated 18-FEB-21 07.05.10 18-FEB-21 07.05.22
  • 1215452 Mark Standrich    Terminated 18-FEB-21 07.05.24 18-FEB-21 07.05.36


can you please help.

Answers

  • mathguy
    mathguy Member Posts: 9,489 Gold Crown

    Open your text file. Copy the text from it, and paste it directly in your post (not as a linked document). Then format the text you pasted in your question. Select text, and use the small "paragraph" symbol on the side (click on it, and select "format as code").

    Don't expect people to open attachments from an unknown source. In real life, an IT professional who does that should be fired for violating basic security rules.

  • jflack
    jflack Member Posts: 1,408 Gold Badge

    The general way to do this is:

    SELECT my_pk, terminated_date
      FROM my_table
     WHERE terminated_date = (SELECT max(terminated_date) FROM my_table)
    
  • BEDE
    BEDE Oracle Developer Member Posts: 2,173 Silver Trophy

    To get the latest 3 terminations in descending order by termination date:

    with tb as (
    SELECT my_pk, terminated_date, row_number() over(partition by 1 order by terminated_date desc, my_pk) rn
      FROM my_table
    )
    select from tb 
    where rn<=3
    


    jflack
Sign In or Register to comment.