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:

*The auction cannot be open and have the item been sold* so when AuctionOpen=0 this means that the auction is not open and the item has been sold and you get results. When AuctionOpen=1 the auction is still open and the item has not been sold and you don;t get results. So what is the problem? — forpas, Jul 18 '19 at 19:05
@forpas Later on I will be using this same operation to get the highest priced item that is still available. — SavageCoder, Jul 18 '19 at 19:20
The sample data you posted are not indicative of the problem you say you have. So what should we do? Speculate? — forpas, Jul 18 '19 at 19:25
Forpas.... the pictures I posted show the result of the query. Not really sure what you want here, bud. — SavageCoder, Jul 18 '19 at 19:35

Gordon Linoff · Accepted Answer · 2019-07-18T19:50:17.840

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.

edited Jul 18 '19 at 19:50

answered Jul 18 '19 at 19:01

Gordon Linoff

1,242,037
58
646
786

This worked when changed to SQL Server syntax. Thank you! – SavageCoder Jul 18 '19 at 19:43

score 0 · Answer 2 · answered Jul 18 '19 at 19:22

0

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

answered Jul 18 '19 at 19:22

Agbesi Innocent

43
8

SQL Multiple Conditions in max statement not working

2 Answers2