0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

set statistics io on

-- select questions where the score is the maximum for any question.
SELECT Title, CreationDate, OwnerDisplayName
FROM Posts
WHERE PostTypeId = 1 AND
  Score = (SELECT MAX(Score) FROM Posts WHERE PostTypeId = 1);

-- select all questions for which there is not any question with a higher score
SELECT Title, CreationDate, OwnerDisplayName
FROM Posts AS P1
WHERE NOT EXISTS (
  SELECT * FROM Posts AS P2
  WHERE PostTypeId = 1 AND P2.Score > P1.Score
  );

-- select all questions that are ranked #1 in order of score, highest-to-lowest
WITH RankedQuestions AS (
  SELECT Title, CreationDate, OwnerDisplayName, Score,
  RANK() OVER (ORDER BY Score Desc) AS ScoreRank
  FROM Posts
  WHERE PostTypeId = 1
  )
SELECT Title, CreationDate, OwnerDisplayName, Score
FROM RankedQuestions
WHERE ScoreRank = 1

fork query

edited dec 11 23

created dec 11 23 stevekass