robin g - 6 months ago 19

SQL Question

My Table

`ItemCode ItemName Total`

----------------------------------

A name1 5

A name1 5

A name2 10

B name1 10

B name2 25

B name1 30

C name2 5

C name1 30

C name1 20

i want to display all

`itemcode`

`A`

`B`

`Total`

My expected Result

`ItemCode ItemName Total`

----------------------------------------

A name1 5

A name1 5

B name2 25

B name1 30

I already asked this question but this time my question is much clearer than my last quesiton. I think 1 solution for this is a self join but i cant figure it out. any help would mean a lot to me thanks!

Answer

Assuming that you need the rows for which does not exist another row of a different `itemCode`

with the same `Total`

, and assuming that `ItemCode`

is always not null, a simple solution can be this:

```
with test(ItemCode, ItemName, Total) as
(
select 'A', 'name1', 5 from dual union all
select 'A', 'name1', 5 from dual union all
select 'A', 'name2', 10 from dual union all
select 'B', 'name1', 10 from dual union all
select 'B', 'name2', 25 from dual union all
select 'B', 'name1', 30 from dual union all
select 'C', 'name2', 5 from dual union all
select 'C', 'name1', 30 from dual union all
select 'C', 'name1', 20 from dual
)
select *
from test t1
where ItemCode in ('A', 'B')
and not exists (
select 1
from test t2
where t1.total = t2.total
and t1.itemCode != t2.itemCode
and ItemCode in ('A', 'B')
)
```

The following is faster, but less readable:

```
select ItemCode, ItemName, Total
from (
select ItemCode, ItemName, Total, count(distinct ItemCode) over (partition by Total) as itemCount
from test
where ItemCode in ('A', 'B')
)
where itemCount = 1
```