Select all rows with the same value in column 1 but different values in columns 2 and 3 using SQL

Question

I have a table which looks like this

Each [Order number] can have 1 or multiple [Line number] and each [Line number] can have status [SHIPPED] OR [UNSHIPPED].

I want to select all the [Order number] which contain [Line number] having both [SHIPPED] and [UNSHIPPED].

For example these [Order number] contain [Line number] with [SHIPPED] and [UNSHIPPED] at the same time so it should be selected

Here is my query but it doesn't return the correct result

SELECT [Order number], [Line number], [SHIPPED/UNSHIPPED] 
FROM [mytable]
WHERE [Order number] IN (SELECT [Order number]
                         FROM [mytable]
                         GROUP BY [Order number]
                         HAVING COUNT(*) > 1)
ORDER BY [Order number], [Line number]

Any suggestions please what is missing in my query ? Thank you.

Answer 1

Here is one approach:

WITH cte AS (
    SELECT [Order number]
    FROM mytable
    WHERE [SHIPPED/UNSHIPPED] IN ('SHIPPED', 'UNSHIPPED')
    GROUP BY [Order number]
    HAVING COUNT(DISTINCT [SHIPPED/UNSHIPPED]) = 2
)

SELECT *
FROM mytable
WHERE [Order number] IN (SELECT [Order number] FROM cte);

The CTE finds all order numbers which have both shipped and unshipped records. It works by first restricting a given order's records to only those having shipped/unshipped, then it asserts that the distinct count of that group is 2, implying both types of shipments are present.

Answer 2

your query was almost right just need a little bit change i did that

    SELECT [Order number],[Line number],[SHIPPED/UNSHIPPED] FROM [mytable]
    WHERE [Order number] IN (
    SELECT [Order number]
    FROM [mytable]
    WHERE [SHIPPED/UNSHIPPED] IN ('SHIPPED', 'UNSHIPPED') --added where clause
    GROUP BY [Order number]
    HAVING COUNT(DISTINCT [SHIPPED/UNSHIPPED]) >= 2 --changed this condition
    )
    ORDER BY [Order number],[Line number]

Answer 3

In opposite to other answer, I think, you mean that [Line number] has to have both statuses, i.e.:

OrderNumber LineNumber UNSHIPPED/SHIPPED
1           20         SHIPPED
1           20         UNSHIPPED
2           30         SHIPPEd
2           40         UNSHIPPED

then, required reuslt would be only [Order number] = 1, since it has line with both statuses.

Accordingly to this logic, here's query:

SELECT OrderNumber,
       LineNumber,
       [Unshipped/Shipped]
FROM (
    SELECT OrderNumber,
           LineNumber,
           [Unshipped/Shipped],
           COUNT(DISTINCT [Unshipped/Shipped]) OVER (PARTITION BY OrderNumber, LineNumber) cnt
    FROM my_table
    WHERE [Unshipped/Shipped] IS NOT NULL
) a WHERE cnt > 1

Or with GROUP BY:

SELECT OrderNumber,
       LineNumber
FROM my_table
WHERE [Unshipped/Shipped] IS NOT NULL
GROUP BY OrderNumber, LineNumber
HAVING COUNT(DISTINCT [Unshipped/Shipped]) > 1