1.按VESSEL_CD, SYEAR, SMonth进行分组,组内数据按SENDDATE排序。
select VESSEL_CD,
SYEAR,
SMonth,
to_char(SENDDATE, 'yyyy-mm-dd hh24:mi:ss') SENDDATE,
dense_rank() over(partition by VESSEL_CD, SYEAR, SMonth order by SENDDATE desc) nmb
from ami_month_oil_lube lube
2.取每组内的第一组数据。
select VESSEL_CD, SYEAR, SMonth, SENDDATE, nmb
from (select VESSEL_CD,
SYEAR,
SMonth,
to_char(SENDDATE,'yyyy-mm-dd hh24:mi:ss') SENDDATE,
dense_rank() over(partition by VESSEL_CD, SYEAR, SMonth order by SENDDATE desc) nmb
from ami_month_oil_lube lube)
where nmb = 1