CODEWITHSUNDEEP
sqlsql-server
dmshow
dmshow

Reputation: 121

SQL Top(2) of a secondary Column

I have a query 

SELECT PR.WBS1, PR.WBS2 FROM PR

It brings back what I need, but want to limit to the top (2) of the WBS2 for each of the WBS1's.

Sample Data

Upvotes: 1

Views: 87

Answers (3)

jophab
jophab

Reputation: 5509

You can use ROW_NUMBER(). It groups the based on WBS1 and then selects two from each group. 

;WITH CTE AS (

    SELECT 
        PR.WBS1, 
        PR.WBS2,
        ROW_NUMBER() OVER( PARTITION BY PR.WBS1 ORDER BY PR.WBS2) AS RN
    FROM 
        PR
)

SELECT 
    WBS1,
    WBS2 
FROM 
    CTE 
WHERE 
    RN <=2

Upvotes: 3

SALEH
SALEH

Reputation: 1562

SELECT PR.WBS1, PR.WBS2 
FROM PR 
WHERE (
    SELECT COUNT(*) 
    FROM PR p
    WHERE p.WBS1 = PR.WBS1 
      AND p.WBS2 >= PR.WBS2
) <= 2;

Upvotes: 3

dmshow
dmshow

Reputation: 121

I had to change a few things on the above answer.

SELECT PR.WBS1, PR.WBS2 
FROM PR 
WHERE (
    SELECT COUNT(*) 
    FROM PR p
    WHERE p.WBS1 = PR.WBS1 
      AND p.WBS2 <= PR.WBS2
) <= 2;

Upvotes: 2

Related Questions