我需要根据行的值删除重复的行,这是在对列的顺序进行排序之后.
我有一个有数百万行的表,为了方便起见,我只对一个‘FileNo_’进行了过滤(我最终会删除该过滤器).
SELECT TOP (100) PERCENT [File No_], [Line No_], [Created on]
FROM dbo.[Shipment Serv_ Fin_ Status]
WHERE ([File No_] = N'TA-P200081258')
ORDER BY [File No_], [Line No_] DESC, [Created on] DESC
这是查询的结果(current results)
File No_ | Line No_ | Created on |
---|---|---|
TA-P200081258 | 10000 | 2020-11-12 00:00:00.000 |
TA-P200081258 | 30000 | 2020-11-12 00:00:00.000 |
TA-P200081258 | 40000 | 2020-11-12 00:00:00.000 |
TA-P200081258 | 50000 | 2020-11-12 00:00:00.000 |
TA-P200081258 | 60000 | 2021-01-27 00:00:00.000 |
TA-P200081258 | 70000 | 2021-01-27 00:00:00.000 |
TA-P200081258 | 80000 | 2021-01-27 00:00:00.000 |
TA-P200081258 | 90000 | 2021-01-27 00:00:00.000 |
TA-P200081258 | 10000 | 2021-02-02 00:00:00.000 |
TA-P200081258 | 20000 | 2021-02-02 00:00:00.000 |
TA-P200081258 | 30000 | 2021-02-02 00:00:00.000 |
TA-P200081258 | 40000 | 2021-02-02 00:00:00.000 |
但我想消除基于‘文件编号_’和‘行号_’列的重复项(在对3列进行排序之后).
所以结果应该是这样的(desired results):
File No_ | Line No_ | Created on |
---|---|---|
TA-P200081258 | 10000 | 2020-11-12 00:00:00.000 |
TA-P200081258 | 30000 | 2020-11-12 00:00:00.000 |
TA-P200081258 | 40000 | 2020-11-12 00:00:00.000 |
TA-P200081258 | 50000 | 2020-11-12 00:00:00.000 |
TA-P200081258 | 60000 | 2021-01-27 00:00:00.000 |
TA-P200081258 | 70000 | 2021-01-27 00:00:00.000 |
TA-P200081258 | 80000 | 2021-01-27 00:00:00.000 |
TA-P200081258 | 90000 | 2021-01-27 00:00:00.000 |
TA-P200081258 | 20000 | 2021-02-02 00:00:00.000 |
有人能帮帮忙吗?