Greater than ( >) with ANY equivalent in sqlite

user_18136471 0
Viewed 115

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
)
where-clause sqlite sql
1 Answers

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);
user_104988280
Related