CODEWITHSUNDEEP
sqloracle-database
Enkimaru
Enkimaru

Reputation: 27

Getting the 2 rows with min and max value from a table

I have a table Product with the following columns:

id  | product_name | price

1   | Red Shirt    | 10.0
2   | White Shirt  | 15.0
3   | Black Shirt  | 9.0
4   | Yellow Shirt | 12.0

How could I make a query that would return the rows that have max(price) and min(price)? Using the example above the output should be:

id  | product_name | price

3   | Black Shirt  | 9.0
2   | White Shirt  | 15.0

In case of repeated values, it doesn't matter which one is selected, as long the output is just 2 rows.

Upvotes: 1

Views: 242

Answers (4)

MT0
MT0

Reputation: 167867

You can do it using the ROW_NUMBER() analytic function which only uses a single table scan (unlike solutions that use UNION or sub-queries).

Oracle Setup:

CREATE TABLE product ( id, product_name, price ) AS
SELECT 1, 'Red Shirt',    10.0 FROM DUAL UNION ALL
SELECT 2, 'White Shirt',  15.0 FROM DUAL UNION ALL
SELECT 3, 'Black Shirt',   9.0 FROM DUAL UNION ALL
SELECT 4, 'Yellow Shirt', 12.0 FROM DUAL UNION ALL
SELECT 5, 'Blue Shirt',    9.0 FROM DUAL

Query:

SELECT id, product_name, price
FROM   (
  SELECT p.*,
         ROW_NUMBER() OVER ( ORDER BY price ASC  ) As min_price_rn,
         ROW_NUMBER() OVER ( ORDER BY price DESC ) As max_price_rn
  FROM   product p
)
WHERE min_price_rn = 1
OR    max_price_rn = 1;

Output:

ID | PRODUCT_NAME | PRICE
-: | :----------- | ----:
 2 | White Shirt  |    15
 3 | Black Shirt  |     9

db<>fiddle here

Upvotes: 2

Shaun Peterson
Shaun Peterson

Reputation: 1790

A couple of the other answers do not cater for the last requirement... 'In case of repeated values, it doesn't matter which one is selected, as long the output is just 2 rows.'

They will return three rows if there is two products with the same value.

the below gets around that by adding the rownum = 1 clause.

with testtab (id, product_name, price) as
(select 1, 'Red Shrit', 10.00 from dual
 union 
 select 2, 'Whtie Shrit', 15.00 from dual
 union 
 select 3, 'Black Shrit', 9.00 from dual
 union 
 select 4, 'Yellow Shrit', 12.00 from dual
 union 
 select 4, 'Pink Shrit', 15.00 from dual)
select id, product_name, price 
from testtab 
where price in (select max(price) from testtab)
and rownum = 1
union
select id, product_name, price 
from testtab where price in (select min(price) from testtab)
and rownum = 1

Upvotes: 2

Gauravsa
Gauravsa

Reputation: 6522

You can do this as well:

select *
from product
where price in (
          (select min(price) from product),
          (select max(price) from product)
);

Or you can do union like below:

-- The UNION approach

Select *
from Product
where price = (select min(price) from Product)
union
select *
from Product
where price = (select max(price) from Product);

Used the following:

Create Table Product(id int, product_name varchar(20), price decimal);

Insert into Product values (1, 'Red Shirt', 10.0);
Insert into Product values (2, 'White Shirt', 15.0);
Insert into Product values (3, 'Black Shirt', 9.0);
Insert into Product values (4, 'Yellow Shirt', 12.0);

Select *
from Product
where Price in (
          (select min(Price) from Product),
          (select max(Price) from Product)
);

Final Output:

Id  Product_Name    Price
2   White Shirt     15
3   Black Shirt     9

Upvotes: 0

Avi
Avi

Reputation: 1845

You can make use of Or condition in your where condition. 

with cte as (select 1 as ID, 'Red Shirt' as Product_name, 10.0 as price from dual 
union all 
select 2 as ID, 'White Shirt' as Product_name, 15.0 as price from dual 
union all 
select 3 as ID, 'Black Shirt' as Product_name, 9.0 as price from dual 
union all 
select 4 as ID, 'Yellow Shirt' as Product_name, 12.0 as price from dual ) 

select * from cte 
where price = (select min(price) minprice from cte) or price = (select max(price) 
maxprice from cte);

Output: 

  ID    PRODUCT_NAME    PRICE
   2    White Shirt     15 
   3    Black Shirt      9

Upvotes: 0

Related Questions