SQL Query is not giving the correct amount of rows?

#1

Hi :slight_smile:

Could someone take a look at this fiddle: http://sqlfiddle.com/#!9/1c2a3b/1

Capture

I’m trying to get a dynamic number of rows depending on a product’s quantity. For example, if I have a quantity of 3 apples in my database, the SQL query should find out if there are 3 or more rows and give a specific result if there are, and a different result if there aren’t.

Can you figure out where the problem is?

If my question is not clear, please let me know where I can elaborate. The current result in the fiddle gives me 2 rows even though there are clearly 3 rows in the database (for product_id #1).

Thank you for stopping by :slight_smile:

#2
SELECT * FROM (SELECT * FROM bids WHERE amount_bid = 
(SELECT IF(COUNT(*) >= 3, MAX(amount_bid), amount_bid) 
 FROM bids WHERE product_id = 1)
) x

More specifically,

WHERE amount_bid = (SELECT IF(COUNT(*) >= 3, MAX(amount_bid), amount_bid)

So, if there are more than 3 bids, show me the bids that equal the highest bid.

In fact, this query gives you way too much data to sort through,

SELECT * FROM (SELECT * FROM bids WHERE amount_bid = 
(SELECT IF(COUNT(*) >= 3, MAX(amount_bid), amount_bid) 
 FROM bids WHERE product_id = 1)
) x

http://sqlfiddle.com/#!9/1c2a3b/27

2 Likes
#3

@astonecipher You’re such an SQL Guru :dizzy_face: never cease to amaze me with your skills :slightly_smiling_face:
I’m going to spend the next 12 hours figuring how you came to that conclusion. It’s perfect!