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';