CODEWITHSUNDEEP
sqloracle-database
Mistu4u
Mistu4u

Reputation: 5416

Compare one column value between row number 1 and row number 2

I have the following table basket for example.

basket fruit   quantity
1      mango   2
1      apple   2
2      banana  2
2      banana  3
2      banana  3

Now I have to find the baskets which have more than 1 row and in the basket the types are different to each other. So basket number 1 should come out.

I have written the following SQL:

select count(*),c.basket from baskets c group by c.basket having count(*)>1;

But after this how can I get the baskets where the fruit types are different to each other among the rows? It should be basket number 1 in this case.

Upvotes: 0

Views: 779

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I would use exists :

select b.*
from baskets b
where exists (select 1 
              from baskets b1 
              where b1.basket = b.basket and 
                    b1.fruit <> b.fruit
            );

Upvotes: 0

wolφi
wolφi

Reputation: 8361

Just add to the HAVING clause:

select count(*), c.basket 
  from baskets c 
 group by c.basket 
having count(*)>1 
   AND COUNT(DISTINCT fruit)>1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would use min() and max():

select b.basket
from baskets b
group by b.basket
where min(b.fruit) <> max(b.fruit);

Upvotes: 1

Related Questions