I’m trying to find the id of the customers whose income is greater than any customer who is 22 years old. I have come up with this query which seems correct to me, but it doesn’t work in SQLite (I get ‘near "SELECT": syntax error’). What is an equivalent query that would work in SQLite?
SELECT id
FROM Customers
WHERE income > ANY (
SELECT C.income
FROM Customers C
WHERE C.age = 22
)
>Solution :
Logically the equivalent condition would be to get the customers whose income is greater than the min income of the customers with age = 22:
SELECT id
FROM Customers
WHERE income > (SELECT MIN(income) FROM Customers WHERE age = 22);
If, in case there are no customers with age = 22, you want your query to return all customers with a salary greater than 0, use also COALESCE():
SELECT id
FROM Customers
WHERE income > (SELECT COALESCE(MIN(income), 0) FROM Customers WHERE age = 22);