CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
Covert
Covert

Reputation: 491

How to select the record with minimum value from table?

I have table BIDS, which contains a lot of columns and rows but I only want to select a row which contains lowest BIDPRICE. 

Select min(Cast(Bids.BidPrice as INT)), BidBidderName from bids BidBidderName

but it throws error

Column 'bids.BidBidderName' is invalid in the select list because it is not contained in either an aggregate function

When I put BidderName in Group by then it shows all records but I only want record which contains lowest bid price.

Upvotes: 0

Views: 379

Answers (4)

A_Sk
A_Sk

Reputation: 4630

try:

Row_Number()

;with cte1 
as
(
select BidderName ,BidPrice,Row_Number() Over(order by price asc) as rn from bids
)
select * from cte1 where rn=1;

Or

Min(BidPrice) Over(PARTITION BY BidderName order by price asc)

select top 1 BidderName ,Min(BidPrice) Over(PARTITION BY BidderName order by price asc) as minBidPrice from bids

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33581

Here is an option that will get just the row with the lowest price.

Select top 1 BidPrice
    , BidBidderName 
from bids
order by Cast(BidPrice as INT)

Upvotes: 1

jpw
jpw

Reputation: 44881

If you only want rows that have the lowest bid price one solution is to use a subquery to find the minimum price like this:

Select  BidPrice, BidBidderName 
from bids 
where BidPrice = (select min(Cast(BidPrice as INT)) from bids)

If the BidPrice is either money or a numeric type (which it most likely should be) the cast to int is not necessary.

If you do 

Select Min(BidPrice) BidPrice, BidBidderName 
from bids 
group by BidBibberName

you would instead get the lowest bid for every bidder.

Upvotes: 0

Roman Marusyk
Roman Marusyk

Reputation: 24579

You can use subquery:

Select BidPrice, BidBidderName from bids 
where Bids.BidPrice in (Select  min(Cast(b.BidPrice as INT)) from bids b)

Or INNER JOIN:

Select b1.BidPrice, b1.BidBidderName from bids b1
inner join (select Min(BidPrice) BidPrice from @bids) as b2 on b.BidPrice = b2.BidPrice

Upvotes: 0

Related Questions