如何使用选择查询SQL基于字段仅对某些值进行分组和拾取

Question

我有一个如下所示的表

ID	ORDERNO
1	223
1	123
2	456
2	456

您可以将TOP WITH TIES用于此

SELECT TOP (1) WITH TIES
t.ID,
t.ORDERNO
FROM YourTable t
ORDER BY
t.ID;

如果你想同时选择和删除，你可以使用OUTPUT子句进行删除

WITH cte AS (
SELECT TOP (1) WITH TIES
t.ID,
t.ORDERNO
FROM YourTable t
ORDER BY
t.ID
)
DELETE cte
OUTPUT deleted.*;

作为一个选项，您可以在MIN(ID(上选择，如：

SELECT *
FROM yourtable
WHERE ID = (SELECT MIN(ID) FROM yourtable);

你也可以使用窗口功能来做到这一点：

SELECT ID, ORDERNO
FROM 
(
SELECT ID, ORDERNO
DENSE_RANK() OVER (ORDER BY ID ASC) AS dr
FROM yourtable
)dt
WHERE dr = 1;

对行进行排序，并选择所需的前n行：

select top (1) with ties ID, ORDERNO
from tablename
order by ID asc