CODEWITHSUNDEEP
sqloracle-database
Victor Suarez
Victor Suarez

Reputation: 45

How can I discriminate rows with similar values in a single select?

I got rows like these in a table:

PRODUCTID | NAME  | LOCATION  | PRICE 
   1          X        R         5
   1          X        C         4.5
   2          Y        R         5
   3          Z        C         4.5

As you can see, the table can contain the same product but in different locations. All of the products are in the 'R' location, but sometimes a product can be in both the R and C Location with different prices.

What I want is that if a product is in both locations R and C, only get the location C, but if it's only in the R location, then just get the R one.

How can I accomplish This?

I have tried using sub queries:

SELECT *
FROM ProductLocation t
WHERE EXISTS (
  SELECT *
  FROM ProductLocation 
  WHERE 
     LOCATION not in ('R')
)
and t.PRODUCTID='1'

Upvotes: 2

Views: 540

Answers (2)

OldProgrammer
OldProgrammer

Reputation: 12169

I think this may work also:

select * from
(select p.*, row_number() over
 ( partition by productid, name 
   order by case when location = 'C' then 1 else 2 end) rnum
from productlocation p)
where rnum = 1

SQLFiddle

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Probably the most efficient way is:

select pl.*
from ProductLocation pl
where pl.location = 'C'
union all
select pl.*
from ProductLocation pl
where pl.location = 'R' and
      not exists (select 1
                  from ProductLocation pl2
                  where pl2.productId = pl.productId and
                        pl2.location = 'C'
                 );

For performance, you want indexes on ProductLocation(location) and ProductLocation(productId, location).

Upvotes: 2

Related Questions