I have 2 tables that I am dealing with, table 1 has identity information (II), columns ID and Name. Table 2 has data collection (DC) information, InfoId, DCId, data, timestamp. The DC table has time series data for each element in the II Table.
II Table
id Name
0 A
1 B
2 C
DC Table
id DCId data timestamp
0 0 5 TS3
1 0 7 TS1
0 1 8 TS10
2 1 3 TS5
1 1 5 TS4
0 2 6 TS2
Where TS10 is later than TS1, etc
Currently I do a select on the II table to get all of the data, and then use a while loop to get the data in the second table for a matching Id and the max DCid.
Which results in
Name Data TS
A 8 TS10
B 5 TS4
C 3 TS5
I would like to do this with one select statement because I would like the result ordered by timestamp, but I am unsure how to get what I want.
Name Data TS
A 8 TS10
C 3 TS5
B 5 TS4
The current code looks similar to this:
Select * from II ;
for ( results )
{
Select Max(DC.DCId) as MaxDCId from DC where DCId=II.sensorInfoId ;
Select Data, timestamp from DC where DCId=:MaxDCId AND DC.ID =II.ID
(Stick results for name, value, and timestamp in HTML table)
}
I am not sure if I need a sub-query join or combinations of both to get what I want (or if it is possible with a single SQL query). I did it this way for expediency…I wanted to get the application running. Now that I want the result table ordered by timestamp, I could cache all the values, sort em and then display the HTML table, but if I could do it with 1 query and let SQL do the work I would rather do that.
Best Answer
In stead of
ORDER BY 3 DESC
, you could also writeORDER BY MAX(DC.TS) DESC
.