求一SQL语句

[纪小北]

在视图VWInfo中对MMSI分组或distinct 查询出所有记录，根据mmsi,createtime 确定最近两条记录的PortNum值，增加字段HangXian， 最近的PortNum<倒数第二条，返回0，反之返回1

[拥抱开源]

需求不明确吧？

[纪小北]

如上图，返回结果为 MMSI Inport CreatTime PortNum Route Hangxian 412051000 58 2013-02-16 13：35:03.000 432 21 1 412065000 38 2013-02-22 08:11:59.000 217 20 0 412109228 11 2013-02-19 21:48:32.000 10 2 0

[学习中-------]

with aa as 
(select row_number()over(partition by mmsi order by createtime desc )n,* from tb)

select distinct a1.*,case when a1.portnum<=a2.portnum then 0 else 1 end as hangxian from (
select * from aa
where n=1)a1 join (select * from aa
where n=2)a2 on a1.mmsi=a2.mmsi