SQL query select rows less than or equal to the (max - min) of an existing column

Question

Let's say I have a table named ages containing one column called age that just contains rows of age integers.

I want to find rows that are less than or equal to the (MAX(age) - MIN(age)).

The approach I first took was doing

SELECT * FROM ages WHERE age <= MAX(age) - MIN(age) which didn't work because we can't use aggregate in the where clause.

IS there anyway one could solve this problem using a sql query?

Answer 1

Two steps:

1. Aggregate the table to find maximum, minimum, and difference.
2. Then compare with your rows.

The query consists of a main query and a subquery:

SELECT * FROM ages WHERE age <= (SELECT MAX(age) - MIN(age) FROM ages);