Select first 10 rows of a column in a table

Can you please let me know how to select first 10 rows for every cusip in the below table

SELECT [dt_dim_id],
       [cusip_num],
       sr.[product_dim_id],
       [acct_dim_id],
       [product_name]
FROM [csr_prod].[dbo].[stock_rec] AS sr,
     [csr_prod].[dbo].[product_dim] AS pd
WHERE sr.product_dim_id = pd.product_dim_id
  AND dt_dim_id = 20180927
ORDER BY dt_dim_id,
         product_dim_id,
         acct_dim_id;

Answer

Use ROW_NUMBER() with a partition over your groups and order by whatever you need, then filter for the first 10 rows:

;WITH paging AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY cusip_num 
                                  ORDER BY dt_dim_id, product_dim_id, acct_dim_id) n,
           [dt_dim_id],
           [cusip_num],
           sr.[product_dim_id],
           [acct_dim_id],
           [product_name]
      FROM [csr_prod].[dbo].[stock_rec] AS sr,
           [csr_prod].[dbo].[product_dim] AS pd
     WHERE sr.product_dim_id = pd.product_dim_id
       AND dt_dim_id = 20180927
)
SELECT * FROM paging WHERE n <= 10