According to the location to retrieve the latest two data-CodePudding

Data in the table below, table, lane, pos, three columns represent the position, remove all locations (table, lane, the same pos) two recent data according to time,
Table data about hundreds of thousands of lines

Id table lane pos wu nan create_time
1 2 1 1 aaa BBBBB 01:03:01 2020-6-25. 456
2 1 2 1 CCCC DDDD 02:00:01 2020-6-25. 456
1 2 3 aaa BBB 03:00:01 2020-6-25. 456
4 2 1 1 333 2020-6-25 FFFF 04:06:01. 456
5 2 1 1 aaa BBB 05:00:01 2020-6-25. 456
6 1 2 1 333 2020-6-25 FFFF 06:00:01. 456
7 1 2 1 aaa BBB 07:00:01 2020-6-25. 456
8 1 1 FFFF 333 2 020-6-25 08:08:01. 456
9 1 1 3 333 2020-6-25 FFFF 09:00:01. 456
10 1 1 3 333 2020-6-25 FFFF 10:00:01. 456
11 1 1 3 aaa BBB 11:09:01 2020-6-25. 456
12 3 2 1 333 2020-6-25 FFFF 12:00:01. 456
13 3 2 1 aaa BBB 13:00:01 2020-6-25. 456
14 3 2 1 333 2020-6-25 FFFF 14:09:01. 456
15 3 2 1 333 2020-6-25 FFFF 15:00:01. 456

CodePudding user response:

 
Select * 
The from (select *, rn=row_number () over (partition by [table], [lane], [pos] order by [create_time] desc) 
The from [table name]) t 
Where t.r n<=2