CODEWITHSUNDEEP
sql-server
Vishal modi
Vishal modi

Reputation: 1720

Get top 10 unique vendor data based on sub query which return vendor id

I have two tables FileMaster and VendorMaster. In VendorMaster i have vendor id and other stuff. in FileMaster i have file related data where 'Vendorid' is foreign key in FileMaster. Now I want to fetch top 10 data from FileMaster for Each 'Vendor' (One record for one vendor).

I have tried below query, but it returns me 10 records with duplicate vendorID

select top 10 * from FileMaster where VendorId in (select  top 10 VendorId from VendorMaster)

Upvotes: 0

Views: 321

Answers (2)

jishan siddique
jishan siddique

Reputation: 1895

Here you can use simple way instead of subquery

Or Anther way you can use CTE click this Link

SELECT DISTINCT FM.* 
FROM FileMaster FM WITH(NOLOCK)
INNER JOIN [dbo].[VendorMaster] VM WITH(NOLOCK)
ON FM.VendorId  = VM.VendorId 
ORDER BY FM.VendorId ASC
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY

For more details OFFSET related check this Link

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13403

You can use ROW_NUMBER. I assumed FileID column for the identity of File Master. By the way, you don't need any subquery 

SELECT TOP 10 * FROM (
    select  *, 
        ROW_NUMBER() OVER(PARTITION BY VendorID ORDER BY FileID) AS RN 
    FROM FileMaster ) AS T
WHERE RN = 1
ORDER BY FileID

Upvotes: 1

Related Questions