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.
This query is a smaller version of a much larger query.
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
So easy in MySql!
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
HAVING COMMENT 0
mean in the MySQL version - Martin Smith 2012-04-09 17:24
=0
then why bother ordering by comment
? All rows will have the same value. I suspect that the reason your question is unanswered is because it is not very clear what you are trying to do - Martin Smith 2012-04-09 17:59
Following latest edit suspect you want something like this
;WITH oc AS
(
SELECT [order],
COUNT([order]) AS order_count
FROM order_comment
GROUP BY [order]
), occ AS
(
SELECT o.*,
order_count AS comment,
ROW_NUMBER() OVER (ORDER BY order_count DESC) AS [rownum]
FROM [order] o
INNER JOIN oc ON oc.[order] = o.id
)
SELECT *
FROM occ
WHERE [rownum] BETWEEN 0 AND 10
ORDER BY [rownum]
The INNER JOIN
will already exclude any rows with no child rows in order_comment
I assume that you don't know that you can use the OVER
clause with aggregate functions.
COUNT(order) OVER(PARTITION BY id) AS [comment]
....
ORDER BY [comment]
in SQL Server you can order by column number from your query as in ORDER BY 1 more info here http://blog.sqlauthority.com/2010/12/27/sql-server-order-by-columnname-vs-order-by-columnnumber/
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
ORDER BY 1