SELECT TOP 100 p.Id, REPLACE(p.Title, ',', ' ') AS title,...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Music Fans

Q&A for music historians, critics, and fans

SELECT TOP 100
      p.Id,
      REPLACE(p.Title, ',', ' ') AS title,
      p.Tags,
      p.CreationDate, 
      p.LastActivityDate,
      CONVERT(INT, ISNULL(p.FavoriteCount, 0)), 
      ISNULL(p.ViewCount, 0),
      ISNULL(v.UpVote, 0), 
      ISNULL(v.DownVote, 0),
      REPLACE(
      REPLACE(
      REPLACE(
      REPLACE(
      REPLACE(p.Body, '<p>', ''),
                      '</p>', ''),
                      ',', '@@@@'),
                      '<code>', '####'),
                      '</code>', '####')
      AS body
FROM
(SELECT Votes.PostID, 
        UpVote = SUM(CASE WHEN VoteTypeId = 2 THEN 1 ELSE 0 END), 
        DownVote = SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END) 
        FROM Votes WHERE VoteTypeId in (2,3) GROUP BY PostId) v
RIGHT JOIN Posts p ON v.PostID = p.id
WHERE p.PostTypeId = 1
ORDER BY p.Id DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results