CODEWITHSUNDEEP
sqlsql-servert-sql
JuniorDev
JuniorDev

Reputation: 301

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

I have a table which looks like this

enter image description here

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

enter image description here

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.

Upvotes: 6

Views: 1374

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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

Your question is specifically asking only about orders, so the result set should have one row per order. I would generally approach this using aggregation:

select [Order number]
from mytable
where [SHIPPED/UNSHIPPED] in ('SHIPPED', 'UNSHIPPED')
group by [Order number]
having min([SHIPPED/UNSHIPPED]) <> max([SHIPPED/UNSHIPPED]);

Another clever method uses intersect:

select [Order number]
from mytable
where [SHIPPED/UNSHIPPED] = 'SHIPPED'
intersect
select [Order number]
from mytable
where [SHIPPED/UNSHIPPED] = 'UNSHIPPED';

If you actually want the line numbers, use JOIN/IN/EXISTS with the above as a subquery. Another method uses window functions.

Upvotes: 1

gordy
gordy

Reputation: 9786

another

with
    shipped as (select distinct [Order number] from mytable where [SHIPPED/UNSHIPPED] = 'SHIPPED'),
    unshipped as (select distinct [Order number] from mytable where [SHIPPED/UNSHIPPED] = 'UNSHIPPED'),
    both as (select [Order number] from shipped intersect select [Order number] from unshipped)

select * from mytable where [Order number] in (select [Order number] from both)

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37337

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

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

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]

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

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.

Upvotes: 4

Related Questions