How do I retrieve latest record in MS SQL?

Question

Let's say I have a table called Students.

++++++++++++++++++++++++++++++++++++++++++++++++++++

id     name          course             date_admitted
1      Pankesh        EEE            2020-10-21 07:52:30.977
2      Pankesh        IT             2020-11-03 11:53:20.976
3      Pankesh        CE             2020-09-11 08:30:29.975

++++++++++++++++++++++++++++++++++++++++++++++++++++

What I want to retrieve is the latest record

2      Pankesh        IT            2020-11-03 11:53:20.976

What is the SQL Query Statement for this instance?

Answer 1

If you want just one row, you can order by and fetch:

select s.*
from students s
order by date_admitted desc
offset 0 rows fetch first 1 row only

Or using top():

select top (1) s.*
from students s
order by date_admitted desc

On the other hand, if you want the latest row per student, then it is a top 1 per group problem. You can use window functions:

select s.*
from (
    select s.*, 
        row_number() over(partition by name order by date_admitted desc) rn
    from students s
) t
where rn = 1