1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for designers and users of artificial or invented languages, consciously developed instead of having developed naturally

WITH Raw AS (
  SELECT
    PostsWithDeleted.Id,
    MIN(PostsWithDeleted.ViewCount) AS ViewCount,
    COUNT(*) AS VoteNo
  FROM PostsWithDeleted, Votes
  WHERE
    PostsWithDeleted.PostTypeId = 1
    AND
    Votes.PostId = PostsWithDeleted.Id
    AND
    Votes.VoteTypeId IN (2, 3)
  GROUP BY PostsWithDeleted.Id
),
Raw2 AS (
  SELECT
    Id, ViewCount, VoteNo,
    DENSE_RANK() OVER (ORDER BY ViewCount DESC) AS ViewsRank,
    DENSE_RANK() OVER (ORDER BY VoteNo) AS VoteRank
  FROM Raw
)
SELECT TOP 100 Id AS [Post Link], *
FROM Raw2
ORDER BY ViewsRank + VoteRank

fork query

created mar 27 19 peterh (2)