I have two tables:
connector_status
| connector | status_timestamp | status |
|---|---|---|
| 1 | 2020-03-03 09:07:09.058000 | available |
| 2 | 2020-03-03 09:51:03.852000 | faulted |
| 1 | 2022-10-06 16:32:14.130000 | charging |
| 3 | 2022-10-06 16:28:26.228000 | available |
| 4 | 2022-10-06 16:28:03.195000 | charging |
connector
| connector | box_id | connector_id |
|---|---|---|
| 1 | Alpha | 0 |
| 2 | Alpha | 1 |
| 3 | Beta | 0 |
| 4 | Beta | 1 |
My connector_status table has multiple rows for each connector, but I only want the most recent row, based on the box_id
I would like to join the tables based on box_id but using the latest timestamp from the 2x connectors. This would select status charging based on the above table
The joined table would look a bit like this:
| connector | status_timestamp | status | box_id |
|---|---|---|---|
| 1 | 2020-03-03 09:07:09.058000 | available | Alpha |
| 2 | 2020-03-03 09:51:03.852000 | faulted | Alpha |
| 1 | 2022-10-06 16:32:14.130000 | charging | Alpha |
| 3 | 2022-10-06 16:28:26.228000 | available | Beta |
| 4 | 2022-10-06 16:28:03.195000 | charging | Beta |
With the desired result:
| box_id | status |
|---|---|
| Alpha | Charging |
| Beta | Available |
I have the following code
SELECT IF(connector_status.status = 'Charging','Charging', IF(connector_status.status ='Available','Not Occupied', IF(connector_status.status = 'Faulted','Faulted','Occupied'))) AS group_status, connector.connector_id, connector.box_id, status_timestamp FROM connector_status JOIN connector ON connector_status.connector = connector.connector GROUP BY connector.box_id ORDER BY connector.box_id
I don't know how to do the join on box_id to get the max timestamp though.
What is confusing me is if i firstly get the latest timestamp from the connector_status table and then try and join by box_id, how can i be sure that it will take the latest connector timestanmp for that box_id
CodePudding user response：
You could use row_number to get the desired result:
select box_id,
status
from ( select status,
box_id,
row_number() over( partition by tbl.box_id order by tbl.status_timestamp desc ) as rn
from ( select status_timestamp,
status,
box_id
from connector_status cs
inner join connector c on c.connector=cs.connector
) as tbl
) as x where rn=1 ;