jeudi 3 décembre 2015

How to use select query with conditions?

Let's say we have a table Events with (sensor_id:integer, event_type:integer, value:integer and time:timestamp).

I want an SQL for each sensor and event type return the most recent value (in term of time). The table should be sorted by sensor_id (asc), event_type(asc).

  sensor_id  | event_type | value      | time
  -----------+------------+------------+--------------------
  2          | 2          | 5          | 2014-02-13 12:42:00
  2          | 4          | -42        | 2014-02-13 13:19:57
  2          | 2          | 2          | 2014-02-13 14:48:30
  3          | 2          | 7          | 2014-02-13 12:54:39
  2          | 3          | 54         | 2014-02-13 13:32:36

Will return:

  sensor_id  | event_type | value
  -----------+------------+-----------
  2          | 2          | 2
  2          | 3          | 54
  2          | 4          | -42
  3          | 2          | 7

Any help?

Aucun commentaire:

Enregistrer un commentaire