CODEWITHSUNDEEP
mysqlsqlselectcountwhere-clause
Anton Ionov
Anton Ionov

Reputation: 21

How the WHERE clause works in the following instance?

I'm having difficulty wrapping my head around the following code:

SELECT
    productCode, productName, buyPrice
FROM
    products a
WHERE
    1 =
        (
        SELECT COUNT(productCode)
        FROM products b
        WHERE b.buyPrice > a.buyPrice
        );

Particularly this part:

WHERE
    1 =
        (
        SELECT COUNT(productCode)
        FROM products b
        WHERE b.buyPrice > a.buyPrice # descending order
        );

Questions: 1. What is selection process inside the statement? 2. How WHERE b.buyPrice > a.buyPrice matching rows b.buyPrice against a.buyPrice? 3. WHERE 1 = (SELECT... What is meaning of 1 at this point?

Upvotes: 1

Views: 35

Answers (2)

The Impaler
The Impaler

Reputation: 48850

The "predicate" in the WHERE clause will evaluate to true for the second most expensive product in the table.

It uses a "correlated subquery" that -- for each row -- finds all the products that are more expensive than the current row, and then count them; this is how a correlated subquery works. The predicate verifies this count is exactly 1.

If you noticed the table is used twice, but with different aliases a and b to make accurate references to the comparisons.

Upvotes: 1

GMB
GMB

Reputation: 222622

The query gives you the product with the second highest price, ties included.

The correlated subquery counts how many rows in the same table have a higher price than the one on the current row - which needs to be 1.

If no ties were involved, you would phrase this with a row-limiting query:

select *
from products
order by buyPrice desc
limit 1, 1

But this would not properly handle ties. Also note that as it is, the query does not properly manage top ties: if there is more than 1 product with the maximum price, your query comes up empty, which is probably not what you want.

In MySQL 8.0, where window functions are supported, you would phrase this more easily and more safely with dense_rank() :

select *
from (
    select p.*, dense_rank() over(order by buyPrice desc) rn
    from products p
) p
where rn = 2

Upvotes: 2

Related Questions