title: SQL Server ORDER BY [aggregation] DESC/ASC
body: <p>I want to be able to sort [comment] by ascending or descending. I've tried performing the aggregation again in the "order by" clause, but that didn't work. I also tried sorting outside of the sub query which let me use the alias [comment], but that only sorted between rows 0 and 10.</p>
  
  <p>This query is a smaller version of a much larger query. </p>
  
  <pre><code>
  SELECT *
  FROM   (SELECT ISNULL((SELECT COUNT("order")
                         FROM   order_comment
                         WHERE  "order" = "order"."id"
                         GROUP  BY "order"), 0)                 AS [comment],
                 Row_number() OVER (ORDER BY "order"."id" DESC) AS [rownum]
          FROM   [order]
          WHERE  ISNULL((SELECT COUNT("order")
                         FROM   order_comment
                         WHERE  "order" = "order"."id"
                         GROUP  BY "order"), 0) > 0) AS [filter]
  WHERE  [rownum] BETWEEN 0 AND 10
  </code></pre>
  
  <p>So easy in MySql!</p>
  
  <pre><code>
  SELECT Ifnull((SELECT COUNT(`order`)
                 FROM   order_comment
                 WHERE  `order` = `order`.`id`
                 GROUP  BY `order`), 0) AS `comment`
  FROM   `order`
  HAVING `comment` > 0
  ORDER  BY `comment` DESC
  LIMIT  0, 10  
  </code></pre>