SQL How to group and select values based on another columns same value-CodePudding

I am trying to query the following in tsql.

I have an Orders table which has Id, OrderId and SubType.

I want to select an OrderId only if all its subtypes have the same value. Please see the example below

For the above data the query should return OrderId 102, because all its rows have the same SubType ('Mirror').

I tried with below query, but was not able to get the desired result set. Please guide.

select OrderId, Subtype
from Orders
Group by OrderId, Subtype

CodePudding user response：

Considering you want to get all the rows a NOT EXISTS, like jarlh mentions is likely the more performant option (especially if you have the relevant indexing):

SELECT Id,
       OrderId,
       SubType
FROM dbo.YourTable YT
WHERE NOT EXISTS (SELECT 1
                  FROM dbo.YourTable sq
                  WHERE sq.OrderId = YT.OrderId
                    AND sq.SubType != YT.SubType);

Other options could be an = ALL (something you don't see often) or some windowed MIN/MAX functions in a CTE/derived table:

SELECT Id,
       OrderId,
       SubType
FROM dbo.YourTable YT
WHERE YT.SubType = ALL (SELECT sq.SubType
                        FROM dbo.YourTable sq
                        WHERE sq.OrderId = YT.OrderId);

WITH CTE AS(
    SELECT Id,
           OrderId,
           SubType,
           MAX(SubType) OVER (PARTITION BY OrderID) AS MaxSubType,
           MIN(SubType) OVER (PARTITION BY OrderID) AS MinSubType
    FROM dbo.YourTable YT)
SELECT Id,
       OrderId,
       SubType
FROM CTE
WHERE MaxSubType != MinSubType;

Most of these solutions assume that SubType isn't NULLable.

CodePudding user response：

Don't group by order and subtype, but by order only, because you want one result row per order, not per order and subtype. Then use a HAVING clause to make sure you only get orders with only one subtype. You can select that subtype with MIN(subtye) or MAX(subtye) then.

For example:

select orderid, min(subtype)
from orders
group by orderid
having min(subtype) = max(subtype)
order by orderid;