0

Please login or register to vote for this query.

(click on this box to dismiss)

Enter Query Description

Q&A for anime and manga fans

-- sample for q 7574131
-- Enter Query Description

WITH weeksyears 
     AS (SELECT w.NUMBER AS week, 
                y.NUMBER AS year
         FROM   (SELECT v.NUMBER 
                 FROM   MASTER..spt_values v 
                 WHERE  TYPE = 'P' 
                        AND v.NUMBER BETWEEN 1 AND 52) w, 
                (SELECT v.NUMBER 
                 FROM   MASTER..spt_values v 
                 WHERE  TYPE = 'P' 
                        AND v.NUMBER BETWEEN 2008 AND 2012) y), 
     topssoreperweek 
     AS (SELECT score, 
                Datepart(week, creationdate)       week, 
                Datepart(YEAR, creationdate)       YEAR, 
                Row_number() OVER (PARTITION BY Datepart(wk, creationdate), 
                Datepart( 
                   YEAR, 
                creationdate) ORDER BY score DESC) row 
         FROM   posts) 
SELECT * 
FROM   weeksyears wy 
       LEFT JOIN topssoreperweek t 
         ON wy.week = t.week 
            AND wy.YEAR = t.YEAR 
WHERE  row = 1 
OR row IS NULL 
ORDER BY wy.YEAR, wy.WEEK

fork query

created sep 27 11 conradfrix