How to query for fields that have AT LEAST a given range

sqlite

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?

Best Answer

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

Related Solutions

SQL Query – How to Query for Leaf Nodes in a Hierarchy Using a Closure Table

I think this is what you want (edited):

-- ml : short name for middle-to-leaf connection
-- rm : short name for root-to-middle connection

SELECT                                     -- show me 
    leaf.*                                 -- all
FROM 
    as_node AS leaf                        -- leaf nodes
  JOIN as_node_closure AS ml               -- which have an ancestor
    ON  ml.descendant = leaf.id            -- 
  JOIN as_node AS middle                   -- (let's call it middle)
    ON  ml.ancestor = middle.id            -- 
    AND middle.name = 'trumpet'            -- with the name 'trumpet'
  JOIN as_node_closure AS rm               -- and this middle
    ON  rm.descendant = ml.ancestor        -- has another ancestor
  JOIN as_node AS root                     -- (which we call root)
    ON  rm.ancestor = root.id              -- which is indeed a root node
    AND root.parent IS NULL                -- as it has no parent
WHERE                                      -- and the distance from root to middle
      rm.depth = 2                         -- is 2 (so middle's absolute level is 2)
  AND leaf.name LIKE '%.wav'
  AND NOT EXISTS                           -- and also the leaf is indeed a leaf node
      ( SELECT *                           -- as it has no children
        FROM as_node AS extra
        WHERE extra.parent = leaf.id
      ) ;

Test at SQL-Fiddle (where 'FT' is used instead of 'trumpet', to match your data.)

SQLite – How to Define Table Structure for Measured Data

Your second approach is not normalized: adding another sensor would require creating a new table, and querying multiple sensors would be horribly complex.

When all four sensors get a new value every second, and if you have multiple tables, then the database would have to update four tables. A single table is certainly more efficient.

For fast queries on time ranges, you need an index on the timestamp column. This implies that every INSERT also needs to update the index.
(If you want fast queries on time ranges for a single sensor, you need another index that contains both the sensor_id and timestamp columns.)

Please note that you do not need AUTOINCREMENT when you never delete rows. (Autoincrement In SQLite)

Best Answer

Related Solutions

SQL Query – How to Query for Leaf Nodes in a Hierarchy Using a Closure Table

SQLite – How to Define Table Structure for Measured Data

Related Question