SQL Select Distinct Top 2
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5503692/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Select Distinct Top 2
提问by MisterXero
If I have a table named [Part] with columns [PartID],[IDNumber], and [Length] and data:
如果我有一个名为 [Part] 的表，其中包含 [PartID]、[IDNumber] 和 [Length] 列和数据：
[PartID] [IDNumber] [Length]
1 Test1 50
2 Test1 60
3 Test2 50
4 Test3 70
How can I select just the top 2 records with a distinct IDNumber? After searching for a bit I have not been able to find a query that does what I want. I would like the results to look like this:
如何仅选择具有不同 IDNumber 的前 2 条记录？在搜索了一点之后，我一直无法找到满足我要求的查询。我希望结果如下所示：
[PartID] [IDNumber] [Length]
1 Test1 50
3 Test2 50
What I have now:
我现在所拥有的：
Select distinct top 2
[PartID],
[IDNumber],
[Length]
from
[Part]
To clarify that the PartID is actually a GUID. I thought writing out the GUID for each record was getting a bit messing in my example data.
澄清 PartID 实际上是一个 GUID。我认为为每个记录写出 GUID 在我的示例数据中有点混乱。
回答by Longha
SELECT DISTINCT TOP 2 PartId, IdNumber, Length
FROM
( SELECT PartId, IdNumber, Length, ROW_NUMBER() over(partition by IdNumber order by Length) Orden
FROM [Ayuda]
) A
WHERE A.Orden = 1
ORDER BY Length
回答by Lamak
SELECT TOP 2 b.*
FROM (SELECT idnumber,
MIN(partid) partid
FROM part
GROUP BY idnumber) a
JOIN part b
ON a.partid = b.partid
ORDER BY b.partid
回答by amit_g
You have not mentioned which row to select for duplicate IDNumber. From your example, assuming Min PartID is to be used, you can use the following query. It would need to be tweaked a bit.
您还没有提到要为重复的 IDNumber 选择哪一行。从您的示例中，假设要使用 Min PartID，您可以使用以下查询。它需要稍微调整一下。
Select Top 2
P.*
From
[Part] P
Inner Join
(
Select
[IDNumber]
,Min([PartID]) As MinPartID,
From
[Part]
Group By
[IDNumber]
) T
On
P.PartID = T.MinPartID
And
P.IDNumber = T.IDNumber -- May not be needed is PartID is primary Key
Order By
P.[PartID]
,P.[IDNumber]
