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?

if you have groups use a window function inside a CTE `ROW_NUMBER() OVER(name order by date_admitted DESC) AS SEQ` then select from cte where `seq = 1` — Umar.H, Nov 06 '20 at 09:14

score 2 · Accepted Answer · answered Nov 06 '20 at 09:13

2

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

answered Nov 06 '20 at 09:13

GMB

216,147
25
84
135

I've edited my question. Please take a look. – TechGeek49 Nov 06 '20 at 09:23
@TechGeek49: this appears to do what you ask for. Are you having a specific problem with the queries I provided? – GMB Nov 06 '20 at 09:39
Not really coz I wanna retrieve the latest data without considering the position of it – TechGeek49 Nov 06 '20 at 12:37
Thanks buddy. Could you mind upvoting this question albeit it's being listed as duplicate? – TechGeek49 Nov 09 '20 at 06:28
I have updated the question. Please take a look – TechGeek49 Nov 10 '20 at 09:14

How do I retrieve latest record in MS SQL?

1 Answers1