How to get the latest Record

User_CC24U

sample.txt

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.

mathguy

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

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

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