SQL Select Distinct Top 2 [英] 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屋!
查看全文