SQL Select Distinct Top 2 [英] SQL Select Distinct Top 2

问题描述

如果我有一个名为 [Part] 的表，其中包含 [PartID]、[IDNumber] 和 [Length] 列和数据:

If I have a table named [Part] with columns [PartID],[IDNumber], and [Length] and data:

[PartID]  [IDNumber]  [Length]
1         Test1       50
2         Test1       60
3         Test2       50
4         Test3       70

如何只选择具有不同 IDNumber 的前 2 条记录?在搜索了一点之后，我一直无法找到满足我要求的查询.我希望结果如下所示:

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:

[PartID]  [IDNumber]  [Length]
1         Test1       50
3         Test2       50

我现在拥有的:

Select distinct top 2
        [PartID],
        [IDNumber],
        [Length]
from
    [Part]

澄清 PartID 实际上是一个 GUID.我认为为每个记录写出 GUID 在我的示例数据中有点混乱.

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.

推荐答案

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

这篇关于SQL Select Distinct Top 2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！