week 3 SQL query
Here are my 3 SQL queries to answer the questions.
1. Select all the data your device sent
SELECT * FROM <table name> WHERE device = '<my device id>';
I can do it with mqtt_message table as well
SELECT * FROM mqtt_message WHERE topic LIKE 'itp/device_14/%';
2. Count the number of records your device sent
SELECT count(*) FROM <table name> WHERE device = '<my device id>';
Note : SELECT count(*) and SELECT count(id) gave me the same value.
3. Get the maximum temperature reading your device sent
SELECT max(<column>) FROM <table name> WHERE topic = '<topic to look at>';
I classified the data using topic instead of sensor_data table. in this way, I can use only one WHERE clause instead of two.
another solution :
SELECT max(reading) FROM sensor_data WHERE device = 'device_14' AND measurement ='temperature';