0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for speakers of other languages learning English

-- TOP(10) 

 SELECT TOP 10 
         Rank() over(order by count(*) desc) [rank]
       , COUNT(id) as post_count
       , OwnerUserId 
  into #data
  FROM posts
  where OwnerUserId is not null
  and owneruserid > 0
  group by OwnerUserId
  order by count(*) desc

/*
 SELECT rank
       , owneruserid
  from #data
*/

select pos, [1],[2],[3], [4], [5], [6], [7], [8], [9], [10]
from(
  SELECT rank
       , post_count
       , rank pos
  from #data
) data
pivot
(
  min(  post_count)
  FOR   
  [rank] IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10])  
) AS pivotcols  
order by pos 


SELECT TOP 10 
         Rank() over(order by count(*) desc) [rank]
       , COUNT(id) as post_count
       , OwnerUserId
  FROM posts
  where OwnerUserId is not null
  and owneruserid > 0
  group by OwnerUserId
  order by count(*) desc

fork query

edited mar 8 20 anonymous

created mar 7 20 andrewngdl