Order by sections

You want to select a query for the following scenario using Microsoft SQL Server 2008

Order Customer Order_Type
1     A        NULL
2     A        NULL
3     B        S
4     C        NULL
5     D        S
6     B        NULL
7     D        S

      

I want to order by the customer who placed the order type S, so the output I expect is as follows

Order Customer Order_Type
3     B        S
6     B        NULL
5     D        S
7     D        S
1     A        NULL
2     A        NULL
4     C        NULL

      

The customer who placed the "Order Type" "S" should appear at the top, but the sales order should still be with the Customer, just like the rest. Here is the query I am using

SELECT *
    FROM CustomerOrder      
    ORDER BY COUNT(CASE WHEN Order_Type = 'S' THEN 1 END) OVER (PARTITION BY Customer) DESC, 
             Customer

      

But the output I get is

Order Customer Order_Type
5     D        S
7     D        S
3     B        S
6     B        NULL
1     A        NULL
2     A        NULL
4     C        NULL

      

This is due to the order in the order.

http://sqlfiddle.com/#!3/3162c/5

+3


source to share


1 answer


Use instead MAX()

:



SELECT *
FROM CustomerOrder      
ORDER BY MAX(CASE WHEN Order_Type = 'S' THEN 1 ELSE 0 END) OVER (PARTITION BY Customer) DESC, 
         Customer;

      

+9


source







All Articles