2

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.

Akhil Batra
  • 581
  • 4
  • 16

2 Answers2

2
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
Sateesh Pagolu
  • 9,282
  • 2
  • 30
  • 48
1

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
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • It worked. Thanks [Shnugo](http://stackoverflow.com/users/5089204/shnugo) I would have upvoted if had enough reputation. – Akhil Batra Oct 06 '15 at 09:38
  • @AkhilBatra, Hi, glad to read this. I pushed you a little ;-) Helped you to help me ;-) – Shnugo Oct 06 '15 at 16:20