CODEWITHSUNDEEP
sql-server
N Raghu
N Raghu

Reputation: 746

SQL Server: How to get the top 'N' max occurrences from each group

I have a below dataset and I am trying to get max occurrences of CID per each OID.

IF OBJECT_ID('TEMPDB..#SS',N'U') IS NOT NULL 
DROP TABLE #SS
GO
SELECT * INTO #SS FROM (
SELECT 1 AS OID,501 AS CID
UNION ALL
SELECT 1 AS OID,503 AS CID
UNION ALL
SELECT 1 AS OID,502 AS CID
UNION ALL
SELECT 1 AS OID,501 AS CID
UNION ALL
SELECT 1 AS OID,501 AS CID
UNION ALL
SELECT 2 AS OID,502 AS CID
UNION ALL
SELECT 2 AS OID,502 AS CID
UNION ALL
SELECT 2 AS OID,502 AS CID
UNION ALL
SELECT 2 AS OID,501 AS CID
UNION ALL
SELECT 1 AS OID,503 AS CID
)A
GO

In above sample dataset, I need to get 2 CID per each OID which occurred maximum times. The expected result could be:

OID CID
1   501
1   503
2   501
2   502

This cannot be a duplicate to SQL Select top frequent records because I need this sub-queries and SQL-Server would not accept ORDER BY in sub-query and eventually I need a ranking function to solve my issue. Ranking function was not used in the link provided.

Upvotes: 2

Views: 115

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

You can do this pretty easily utilizing ROW_NUMBER. Thanks for Tab Alleman for the clarification on your requirements.

select *
from
(
    select *
        , RowNum = ROW_NUMBER() over (partition by OID order by count(*) desc)
    from #SS
    group by OID
        , CID
) x
where x.RowNum <= 2
order by x.OID
    , x.CID

Upvotes: 3

Related Questions