MisterXero MisterXero - 1 year ago 118
SQL Question

SQL Select Distinct Top 2

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

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

What I have now:

Select distinct top 2

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.

Answer Source
SELECT DISTINCT TOP 2 PartId, IdNumber, Length
(   SELECT PartId, IdNumber, Length, ROW_NUMBER() over(partition by IdNumber order by Length) Orden
    FROM [Ayuda]
) A
WHERE A.Orden = 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download