sql查各型号最新的记录

需要写一条查询语句排遣寂寞：MSSQL

select  distinct * from TableXXX a
where exists(select * from (select SecId, max(EffectiveDate) as LatestDate from TableXXX group by SecId) x
where a.SecId=x.SecId and a.EffectiveDate = x.LatestDate ) and a.SecId in ('E0GBR00MUG',
'E0GBR017E9',
'E0GBR005M3',
'E0GBR00KQG',
'E0GBR017PJ',
'E0GBR04DXC',
'E0GBR00D6A',
'E0GBR003OI',
'E0GBR00NGD',
'E0GBR00BAG',
'E0GBR01NRL',
'E0GBR010IO',
'E0GBR017FJ',
'F00000H0QA',
'E0GBR004NL',
'E0GBR00SKM',
'E0GBR003I5',
'E0GBR04E6V',
'E0GBR006J6',
'E0GBR017P8',
'F00000SY0G',
'E0GBR01AHE',
'E0GBR00CS6',
'E0GBR01ACI',
'E0GBR0059V',
'E0GBR00EHK',
'E0GBR00BV3',
'E0GBR01JX0',
'E0GBR006D5',
'F000000BD9',
'E0GBR006GM',
'F00000TORD',
'E0GBR04BLL',
'E0GBR0081V')order by SecId

效果：

mysql 的写法：

select h.SecuCode,g.* from LC_ShareStru g
inner join SecuMain h
on g.CompanyCode = h.CompanyCode
inner join

(
select a.CompanyCode,max(a.EndDate) as Ending  from  LC_ShareStru a
inner join SecuMain b
on a.CompanyCode = b.CompanyCode
where a.EndDate <= '2018-05-02' 
and b.SecuCode in  ('600000',
'600016',
'600019',
'600028',
'600029',
'600030',
'600036',
'600048',
'600050',
'600104',
'600111',
'600309',
'600340',
'600518',
'600519',
'600547',
'600606',
'600837',
'600887',
'600919',
'600958',
'600999',
'601006',
'601088',
'601166',
'601169',
'601186',
'601211',
'601229',
'601288',
'601318',
'601328',
'601336',
'601390',
'601398',
'601601',
'601628',
'601668',
'601669',
'601688',
'601766',
'601800',
'601818',
'601857',
'601878',
'601881',
'601985',
'601988',
'601989',
'603993')
group by a.CompanyCode
) as d on

g.CompanyCode = d.CompanyCode and g.EndDate = d.Ending