Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187.1K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 442 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
How to get the latest Record

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
-
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.
-
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)
-
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