Help with SQL to look at rows and return the latest date from an array

Transact-SQL
1

Hi
I am new to SQL and any help is appreciated.
select person_generated_identifier as id, g.division_id as Div_id , g.programme as prog, g.expected_completion_date as Date_completed
from events ,
unnest(Goals) g
WHERE person_generated_identifier = '228018'
the result i get from my table is
id Div_id prog Date_completed
228018 21 Contemporary Art Practice - Performance (MA) 2019-06-27 22:00:00 UTC
228018 25 MRes RCA - Arts & Humanities (MRes) 2020-09-10 22:00:00 UTC
what I do need to get is to adjust my script so that it will look at the whole table for the above (228018) user and will return the row for the lastest date_completed i.e. only returns on row with div_id 25 and completion date 2020-09-10. Basically, I need just one row per user specified by looking at the latest completion date and return the result

I hope this makes sense

Any help is aprreciated

2

row number ... partition by user order by date desc

where row number = 1

3

Thank you for your reply. i did try this but the problem is if the lates date is ow number 2 then it will not show that, I need the latest date completed , this could be row 3 2 or 1

4

order by date desc will do this

image

5

Welcome @frankraz

Always provide sample data as follows.

use sqlteam
go

create table #sample(id int, Div_id int, prog nvarchar(150), Date_completed datetime2)

insert into #sample
select 228018, 21, 'Contemporary Art Practice - Performance (MA)', '2019-06-27 22:00:00' union
select 228018, 25, 'MRes RCA - Arts & Humanities (MRes)', '2020-09-10 22:00:0'  union
select 228019, 21, 'Contemporary Art Practice - Performance (MA)', '2019-06-27 22:00:00' union
select 228019, 25, 'MRes RCA - Arts & Humanities (MRes)', '2020-09-10 22:00:0' 

drop table #sample

if you will always have a id filter you can simply do top 1

select top 1 *
From #sample
where id = 228018
order by Date_completed desc

if you will not filter and it involves a lot of ids

;with src
as
(
select *, ROW_NUMBER() OVER(PARTITION BY id
       ORDER BY Date_completed DESC) AS rowNum 
From #sample
)
select id , Div_id, prog, Date_completed
  from src
where rowNum = 1
6

Thank you for all your help. this worked perfectly