That works fine as long as you don't care which two rows get returned from those 10 with the most recent hiredate. Your solution will work, but only if Sharma fixes the data to be more specific in terms of the date and time of the insert.
Sharma wants THE LAST TWO RECORDS INSERTED INTO THE DATABASE! Not just any two of all the records stored on the most recent date. If there are three records stored in the database on that same day, which two will you get? You don't know unless the time is stored also. A timestamp should be used here, not just a date. Then your solution is perfect.
What everyone is saying is true - if you want the absolute last two records inserted, you need some field that tells you when, precisely, they were inserted.
I would caution against simply calling this field 'timestamp'. The name timestamp is customarily used for a column that is used for optimistic concurrency, and should really be considered 'reserved' for that purpose. If most people were to see a field named timestamp, they would assume that it was used for optimistic concurrency. *In which case*, it would indeed be filled upon insert *but also* modified upon update. This would not, then, be the functionality you would desire.
Therefore, if you need to set *and keep* a column with the precise time the record was inserted, I would certainly suggest a trigger-populated DATE column, but I might suggest calling it something like 'Inserted_TS' instead, to better identify its use.
Now, I didn't have a timestamped column handy so I used the PK. I'm most interested in using a column that's indexed, as I assume the Inserted_TS would be. Here are some results on an ~11,000 record table:
SELECT /* test8 */
FROM org e1
WHERE 2 > (
FROM org e2
WHERE e2.org_pk > e1.org_pk);
These were done, as always, multiple times on an isolated server.
What to note:
- If you can get the optimizer to do an INDEX SCAN DESCENDING, this is your best bet. Note how simply switching to * from a column list did this (wierd, huh?) . Of course, one would *never* use SELECT * in a production-query. Hinting it would be a better idea.
- DO NOT use the > COUNT sub-select proposed by akkerend. Sorry akkerend, but as the results show, that one was pretty bad . Not that it's your fault, I've seen this solution a lot of places too, including from Joe Celko. It just goes to show that nobody is perfect
Again, just trying to raise the awareness of how drastically one's choice of solutions can affect performance.