Power By 专否社区

SQL Server ORDER BY [aggregation] DESC/ASC

Go To StackoverFlow.com

2

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
2012-04-05 23:06
by Jason Fuller
Don't use your where in the sub-query. Put the main logic into the sub-query then sort (order by) and filter (where) the results of the sub-query - Andrew Savinykh 2012-04-05 23:25
@zespri That will mess up the [rownum]. Say only one row is returned, [rownum] might be 40 which doesn't work when your selecting between 0 and 10. That was a major issue was having before this one - Jason Fuller 2012-04-05 23:31
How does this work even without sorting? if it is 40, then your where will filter it right out and it won't be returned - Andrew Savinykh 2012-04-05 23:41
@zespri Using the where in the sub query returns rows starting from 1. Using [filter].[comment] in the where clause on the outer query returns rows at their literal location - Jason Fuller 2012-04-06 15:55
why would you use [filter].[comment] in the where clause? What are you trying to achieve by that? It is not in your example and you never indicated that you want to filter by comment - Andrew Savinykh 2012-04-07 00:05
Sorting orders by highest or lowest comments. It's not in my example because everything I've tried hasn't worked. I'm extremely annoyed by how complicated this is. I had this working in just a couple minutes with MySql - Jason Fuller 2012-04-08 18:57
Yep, I understand about sorting, but this is not what I'd like to clarify. Why would you use [filter].[comment] in the where clause? Do you perceive that it somehow helps you with sorting - Andrew Savinykh 2012-04-08 23:31
Oh, I'm sorry. I wouldn't use it in the where clause. I was thinking of the order by clause. However, I would like to be able to do [filter].[comment] > 0 or = 0 in the where clause, but I don't think that will work in my case - Jason Fuller 2012-04-09 15:37
I added a MySql version of the query to my question. I want the same effect in SQL Server. I even started a bounty so whoever answers this gets +50 - Jason Fuller 2012-04-09 17:11
Please give example data and desired results. Not clear what you are trying to do. What does HAVING COMMENT 0 mean in the MySQL version - Martin Smith 2012-04-09 17:24
It's the comment count. HAVING comment = 0 means find orders that have 0 comments. HAVING comment > 0 means find orders that have comments. This works in MySQL. The issue is with SQL Server - Jason Fuller 2012-04-09 17:35
@JasonFuller - You have omitted any comparison operator in the MySQL version so which should it be - Martin Smith 2012-04-09 17:37
@JasonFuller - You're better off just posting example data. Your query still makes no sense. If you are filtering for =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
@MartinSmith Obviously that was a mistake. It can be "= 0" or "> 0." The issue is sorting this by DESC/ASC in SQL Server. This already works in MySql - Jason Fuller 2012-04-09 18:00
@MartinSmith It makes sense if it's ">" which I now have in my example. This query is a working example. I can cut/paste the above query in HeidiSql and get correct results. I've only omitted fields like the order id and customer data. All that is irrelevant - Jason Fuller 2012-04-09 18:09

5

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

2012-04-09 18:07
by Martin Smith
Thank you! This worked - Jason Fuller 2012-04-09 18:14
It says I can award you the bounty in 22 hours - Jason Fuller 2012-04-09 18:19

0

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]
2012-04-05 23:20
by Rango
Thank you. I didn't know that. I'm looking into that now. It might have to wait until tomorrow before I get the results - Jason Fuller 2012-04-05 23:34
Still working on this, but I'm not sure if you noticed that the COUNT(order) is in a subquery. You can't use over() on subqueries - Jason Fuller 2012-04-06 18:48

0

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
2012-04-05 23:53
by rlobban
That just sorts between rows 0 and 10. Using the alias [comment] or 1 does not make a difference - Jason Fuller 2012-04-06 16:08
Ads