Search code examples
sqlmaxconditional-statementssql-server-2016-express

SQL Multiple Conditions in max statement not working

I am attempting to filter my table and get the item that sold for the most amount of money. In order to do this I am using "AuctionOpen" to determine whether or not the auction is still open. The auction cannot be open and have the item been sold (later I will use this for the most expensive item available).

I am able to use the AND operator to compare AuctionOpen by using the following:

select s.*
from auctionsite.dbo.Auction s
where s.HighestBid = (select max(s2.HighestBid) from auctionsite.dbo.Auction 
   s2) and s.AuctionOpen = 0;

When I set this equal to zero I get results, but when I set it equal to 1, it only returns the column titles even though there are values set to 1 in the table.

Results when compared to 0:

table

Results when compared to 1:

other table

Solution

  • Clearly, the highest bid is on a record where AuctionOpen <> 1.

    I recommend using order by and fetch (or the equivalent in your database):

    select s.*
from auctionsite.dbo.Auction s
where s.AuctionOpen = 0
order by s.HIghestBid desc
fetch first 1 row only

    In SQL Server, use either select top (1) or offset 0 rows fetch first 1 row only.