# How do I query for fields that have AT LEAST a given range?

Say I have a table of the form:

``````+-----+-------+
| Key | Value |
+-----+-------+
| A   |     1 |
| A   |     3 |
| A   |     4 |
| B   |     2 |
| B   |     3 |
| C   |     3 |
| C   |     4 |
| C   |     5 |
+-----+-------+
``````

I want to be able to return all of the keys that have at least a given range.

For example:

A query for a Value range of 2 would return A, B, C
A query for a Value range of 3 would return A, C
A query for a Value range of 4 would return A

Can this be done with SQL (specifically SQLite)? If so, how?

-
What if the value has gaps? Is a range of 4 a contiguous series of 4 values? Or just the difference between `MIN` and `MAX` for that key? – Martin Smith Jan 19 '14 at 22:56
It's the difference between MIN and MAX for that key. I've updated the question to reflect this. – Jamie Bullock Jan 19 '14 at 23:08

To find those with a "range" of at least 4 for example

``````SELECT "Key"
FROM   YourTable
GROUP  BY "Key"
HAVING (1 + MAX("Value") - MIN ("Value")) >= 4;
``````

Obviously alter the `4` as appropriate for the other cases.

SQL Fiddle

-
Perfect, thanks! – Jamie Bullock Jan 20 '14 at 0:56