CODEWITHSUNDEEP
sql-serversubquery
Akhil Batra
Akhil Batra

Reputation: 610

sql server: looping through a subquery and limting result in main query corresponding to evry row in subquery

I am working on stackoverflow sqlquery portal. I want to have top 1000 tags with maximum post count. and for each tag need id of 60 post which have that tag as their tag. so i need to loop across all 100 tags and and get top 60 postid linked with them. sombody help please.

her's my sql statement.

SELECT Top 60 PostId,ok.Id
FROM PostTags as test
INNER JOIN (SELECT TOP 50
Id,Count
FROM Tags
ORDER BY Tags.Count DESC) as ok
ON ok.Id = test.TagId

but it returns only 60 posts but i need 60 for each top tag. Please tell me how to iterate through the subquery.

Upvotes: 2

Views: 698

Answers (2)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

WITH CTEM AS
(
   SELECT TOP 100 T.ID
   FROM Tags T ORDER BY T.[COUNT] DESC
),
CTE as
(
SELECT P.POSTID,T.ID
      ,ROW_NUMBER() OVER(PARTITION BY T.ID ORDER BY P.POSTID DESC) AS RN
FROM PostTags P
        INNER JOIN CTEM T ON P.TAGID = T.ID
)

SELECT * FROM CTE WHERE RN<=60

Upvotes: 2

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Try it like this: The query will fetch three objects out of sys.objects of each type

Post this to an empty query window, adapt to your needs...

WITH TypesToGroup AS
(
    SELECT DISTINCT type_desc FROM sys.objects 
)
select * 
from TypesToGroup
cross apply
(
    select top 3 x.*
    from sys.objects as x
    where x.type_desc=TypesToGroup.type_desc
) AS ThreeOfEach

Upvotes: 1

Related Questions