Forum Stats

  • 3,827,614 Users
  • 2,260,798 Discussions
  • 7,897,317 Comments

Discussions

How to get the latest Record

User_CC24U
User_CC24U Member Posts: 6 Green Ribbon
edited Feb 19, 2021 3:29PM 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: 10,539 Blue Diamond

    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,518 Bronze Trophy

    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,427 Gold 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