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 [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.
Answer. Yes, the DISTINCT clause can be applied to any valid SELECT query. It is important to note that DISTINCT will filter out all rows that are not unique in terms of all selected columns.
Select TOP 2 * from Products where Price = (Select Max(Price) from Products);
Use the Query Plan To Confirm Order Notice that the query “DISTINCT TOP 10” includes the first 10 rows from the query from the “DISTINCT” query. From this we know a DISTINCT list is first created, and then the TOP 10 items returned.
TOP and DISTINCT clause. TOP Clause: TOP is mainly used to limit the result of query in terms of number or percentage of the result from database. It selects result limits to TOP from N numbers of data from ordered rows by using ORDER BY statement otherwise it selects undefined order 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With