'First two records of each group based on rank

I've created a part of the query that returns me the data like in the picture below: Now, I am trying to select First 2 records (1 and 2) of each group (sap_id, wr_nbr) where "rn" has more than 1.

enter image description here

So, my final table should look like:

enter image description here

I've tried with TOP 2 WITH TIES but it returns me only two records of the whole table. Any idea how to achieve this?

Thank you in advance.

sqlteradata


Solution 1:[1]

SELECT b.*
FROM
(SELECT a.[sap_id]
,a.[wr_nbr]
,a.[start_date]
,a.[end_date]
,a.[vs_ind]
,a.[rn]
,COUNT(*) OVER (PARTITION BY a.sap_id, a.wr_nbr) as count_rows
FROM
(SELECT [sap_id]
  ,[ts_nbr]
  ,[wr_nbr]
  ,[check_line]
  ,[check_nbr]
  ,[start_date]
  ,[end_date]
  ,[vs_ind]
  ,[rn]
  ,[rank_ind]
   FROM [dbo].[first_two]) a) b
WHERE b.count_rows > 1
AND b.rn <= 2

Final table looks like:

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Hiten004

Related Questions