0

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.

0
SELECT 
   Name,
   SUM(DC.data) AS Data,
   MAX(DC.TS) AS TS
FROM II
INNER JOIN DC ON DC.id = II.id
GROUP BY II.Name
ORDER BY 3 DESC

In stead of ORDER BY 3 DESC, you could also write ORDER BY MAX(DC.TS) DESC.

|improve this answer|||||
  • Thanks I will see how this works. The while loop is in code. I would like to remove it and consolidate the select. – Joe Simon Feb 14 at 18:27
  • That is really close, I think it will get me where I need to go ! If I need further assistance I will create a new post with a "Minimal, Complete, and Verifiable Example" – Joe Simon Feb 14 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.