Search code examples
sqlsqlitewhere-clause

Greater than ( >) with ANY equivalent in sqlite

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