select NAME , OPT , OPTDATE from ( select NAME , OPT , OPTDATE,row_number() over(partition by name order by OPTDATE desc ) rn from A ) where rn=1
select a.NAME,a.OPT,a.OPTDATE from a inner join (select NAME,max(OPTDATE) as maxOPTDATE from a group by NAME) b on a.NAME=b.NAME and a.OPTDATE=b.maxOPTDATE;
== 思想重于技巧 ==
select NAME , OPT , OPTDATE from ( select NAME , max(OPTDATE) maxdate from A) B , A ) where A.NAME=B.NAME and A.OPTDATE=B.maxdate;
select NAME ,OPT ,OPTDATE from ( select NAME ,OPT , max(OPTDATE) OPTDATE from A) order by OPTDATE ;
select NAME , OPT , OPTDATE,row_number() over(partition by name order by OPTDATE desc ) rn from A
)
where rn=1
from a inner join (select NAME,max(OPTDATE) as maxOPTDATE from a group by NAME) b on a.NAME=b.NAME and a.OPTDATE=b.maxOPTDATE;
select NAME , max(OPTDATE) maxdate from A) B , A
)
where A.NAME=B.NAME and A.OPTDATE=B.maxdate;
select NAME ,OPT , max(OPTDATE) OPTDATE from A) order by OPTDATE ;