SQL Multiple Conditions in max statement not working

Question

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:

Results when compared to 1:

Answer 1

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.

Answer 2

I think you should try the Count aggregate function

here, try this:

    **Select count(Item_name) As 

[Item with the highest money] from table_name Group by Item_name DSEC;**

You can check my page hereSQL/MySQL tips for some SQL/MySQL lessons