CODEWITHSUNDEEP
mysqlsql
MichalB
MichalB

Reputation: 3341

MySQL - how to retrieve the last recorded values grouped by one of the fields?

I have a table node_weather that looks like this:

+--------------------+--------------+------+-----+--------------------+
| Field              | Type         | Null | Key |                    |
+--------------------+--------------+------+-----+--------------------+
| W_id               | mediumint(9) | NO   | PRI | ement              |
| temperature        | int(10)      | YES  |     |                    |
| humidity           | int(10)      | YES  |     |                    |
| wind_direction     | int(10)      | YES  |     |                    |
| wind_speed         | int(10)      | YES  |     |                    |
| wet_temperature    | int(10)      | YES  |     |                    |
| P_id               | mediumint(9) | YES  | MUL |                    |
| time               | timestamp    | NO   |     | CURRENT_TIMESTAMP  |
| level_current      | int(10)      | YES  |     |

In this table, I am recording the data from 4 different weather stations, defined by P_id field (=> P_id can be of values 1, 2, 3 or 4). The data is continuosly filling the table, coming from all the stations.

How to create a query to get the last recorded row for each of the weather stations?

Upvotes: 0

Views: 50

Answers (3)

Charvee Shah
Charvee Shah

Reputation: 720

Try this query. It is faster than sub query..

select nw1.* from node_weather nw1
LEFT JOIN node_weather nw2 on nw2.P_id = nw1.P_id and nw2.time>nw1.time
where nw2.W_ID is null;

Upvotes: 1

Arion
Arion

Reputation: 31239

Maybe something like this:

SELECT
    *
FROM
    node_weather
JOIN
(
    SELECT
        tblInner.P_id,
        MAX(tblInner.time) AS maxTime
    FROM
        node_weather as tblInner
    GROUP BY
        tblInner.P_id
) AS tblMax 
    ON tblMax.maxTime = node_weather.time 
    AND tblMax.P_id = node_weather.P_id

this will get the latest combination with max time and P_id

Upvotes: 2

Caweren
Caweren

Reputation: 244

SELECT * FROM node_weather GROUP BY P_id ORDER BY time DESC

That should do the trick

Upvotes: 0

Related Questions