1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for linguists, teachers, and students wanting to discuss the finer points of the Latin language

SELECT  link, cnt, id [Post Link]
FROM    (
        SELECT  link, id,
                COUNT(*) OVER (PARTITION BY link) cnt,
                ROW_NUMBER() OVER (PARTITION BY link ORDER BY score DESC) rn
        FROM    (
                SELECT  id, score,
                        'http' + SUBSTRING(start, 1, PATINDEX('%/%', SUBSTRING(start, 13, 30)) + 12)
                FROM    (
                        SELECT  id, score,
                                SUBSTRING(body, PATINDEX('%://xkcd.com/[0-9]%', body), 20) start
                        FROM    (
                                SELECT  TOP 400000
                                        id, body, score
                                FROM    posts
                                ORDER BY
                                        id DESC
                                UNION
                                SELECT  TOP 4000000
                                        postId, text, score
                                FROM    comments
                                ORDER BY
                                        id DESC
                                ) q
                        WHERE   body LIKE '%://xkcd.com/[0-9]%'
                        ) q
                ) q (id, score, link)
        ) q
WHERE   rn = 1
ORDER BY
        cnt DESC

view current version

edited jul 9 14

created jul 9 14 Quassnoi