CODEWITHSUNDEEP
sqlsql-server-2008
Anonymous Me
Anonymous Me

Reputation: 1238

Order By Having Certain Value

Need a Select 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

I want to Group Customer who have placed Order Type S, So the output I am expecting is

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

SELECT Order, Customer, Order_Type FROM CustomerOrder GROUP BY Customer,Order, Order_Type HAVING {?}

Upvotes: 0

Views: 92

Answers (5)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this out:

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

I assume you basically want to order your data, placing on top groups of customers having at least one 'S'.

SQL Fiddle Demo

Edit: You have to add:

CASE WHEN Order_Type = 'S' THEN 0 ELSE 1 END

at the tail of the ORDER BY clause if you want to always place 'S' records in the first place of their group.

SQL Fiddle Demo

Upvotes: 3

Jeff
Jeff

Reputation: 2871

GROUP BY is for grouping multiple rows together into a single row, based on a common column value.

For instance, you might get a count of how many orders a particular customer has made like so:

SELECT Customer, COUNT(*) FROM Table
GROUP BY Customer

That would get you output listing the customer name and the number of order rows they had.

If you want to limit this (like a WHERE clause), you use HAVING.

SELECT Customer, COUNT(*) FROM Table
GROUP BY Customer
HAVING Count(*) > 1

This will get you all the Customers who have more than 1 order.

If you want to organize your output, you can sort using ORDER BY.

SELECT * FROM Table
ORDER BY Order_Type

You can even use multiple columns to sort by. It will first sort based on the first column, and then it will sort those groups (rows whose first column all match) by the second, and so on.

SELECT * FROM Table
ORDER BY Order_Type, Customer

EDIT:

With other answers, it has become clear to me what you're really asking. I'll leave my answer above as-is, because a basic explanation of GROUP and ORDER is potentially useful to people who might find this question in the future.

As others have said, if you wish to organize your table such that all customers who have any row that fits criteria X (in this case, 'Order_Type=S'), you can't do it with a simple query or a HAVING clause. As is pointed out, there are numerous ways to do this.

Upvotes: 1

Serge
Serge

Reputation: 417

You also can use temporary table:

DROP TEMPORARY TABLE IF EXISTS tmp_1;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_1 AS (
SELECT
   *
FROM CustomerOrder
WHERE Order_Type is not null
ORDER BY Order_Type DESC
);

SELECT t1.* FROM CustomerOrder t1
LEFT JOIN tmp_1 t2 ON t2.Customer = t1.Customer

Upvotes: 0

fancyPants
fancyPants

Reputation: 51888

What you're looking for is an ORDER BY, not a GROUP BY.

Try this:

SELECT
t.[Order],
t.Customer,
t.Order_Type
FROM
t
LEFT JOIN (SELECT Customer FROM t WHERE Order_Type = 'S') s
ON t.Customer = s.Customer
ORDER BY
s.Customer DESC

What we do here, is simply to add the information, if each customer has an order of order_type S. With the LEFT JOIN this customer number is either the customer number if he has, or NULL. So we order DESCending, and then by whatever you want.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

This seems to produce the result you're looking for:

declare @t table ([Order] int,Customer char(1),Order_Type char(1))
insert into @t([Order],Customer,Order_Type) values
(1,'A',NULL),
(2,'A',NULL),
(3,'B','S' ),
(4,'C',NULL),
(5,'D','S' ),
(6,'B',NULL)

;With TypeS as (
    select *, --TODO - Column list
     MAX(CASE WHEN Order_Type = 'S' THEN 1 ELSE 0 END)
     OVER (PARTITION BY Customer) as SeenS
    from @t
)
select * --TODO - Explicit column list if SeenS shouldn't appear
from TypeS ORDER BY SeenS desc,Customer,[Order]

We use a window function MAX(...) OVER (PARTITION BY ...) in order to work out, on each row, whether any row for that customer has an S order type.

Results:

Order       Customer Order_Type SeenS
----------- -------- ---------- -----------
3           B        S          1
6           B        NULL       1
5           D        S          1
1           A        NULL       0
2           A        NULL       0
4           C        NULL       0

Upvotes: 0

Related Questions