1

I have a table named sales and it has 2 columns: Sales_Id and Price. I need to retrieve the cheapest price from the table. The cheapest price is 100. However, it shows only for one Sales_ID whereas I want it to return all 3 entries with the cheapest price.

Create table sales
(
   Sales_Id int Primary Key,
   Price number(4)
);
insert into sales values( 1,100);
insert into sales values( 2,400);
insert into sales values( 3,100);
insert into sales values( 4,100);
tmwoods
  • 2,353
  • 7
  • 28
  • 55
MRS
  • 45
  • 3

3 Answers3

3
select sales_id
from sales
where price = (select min(price) from sales)
juergen d
  • 201,996
  • 37
  • 293
  • 362
2

One method uses rank()/dense_rank():

select s.*
from (select s.*, rank() over (order by price) as seqnum
      from sales s
     ) s
where seqnum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0
select * from sales
order by price
fetch first row with ties;

(Requires Oracle 12.1 or later.)

William Robertson
  • 15,273
  • 4
  • 38
  • 44