postgrel gp 重复id 取时间最新的一条数据并插入到新表中

插入新表的意思
create table aaaaafoshanCT as
相同visitnumber有多条数据时，选择时间最新的一条数据。

(
select patientid,visitnumber,obssitename,findings,obsdiagtext,lastupdatedttm
, max(lastupdatedttm) over(partition by visitnumber) as lastupdatedttmMax
from obs_report where  obssitename not like '%头%' and obssitename like '%腹%' and findings is not null and servicesectionid='CT'
) x   

外面一层的条件

where lastupdatedttm = lastupdatedttmMax

总的sql语句如下

create table aaaaafoshanCT as
(select * from 
(
select patientid,visitnumber,obssitename,findings,obsdiagtext,lastupdatedttm
, max(lastupdatedttm) over(partition by visitnumber) as lastupdatedttmMax
from obs_report where  obssitename not like '%头%' and obssitename like '%腹%' and findings is not null and servicesectionid='CT'
) x
where lastupdatedttm = lastupdatedttmMax)