SQL query to get the "latest" value for each location

Question

Hi

What I thought would be a simple thing to solve has now bugged for quite some time. Now I need help from you guys.

In Informix I have a table "temperatures" like this:

locId    dtg               temp
100      2009-02-25 10:00  15
200      2009-02-25 10:00  20
300      2009-02-25 10:00  24
100      2009-02-25 09:45  13
300      2009-02-25 09:45  16
200      2009-02-25 09:45  18
400      2009-02-25 09:45  12
100      2009-02-25 09:30  11
300      2009-02-25 09:30  14
200      2009-02-25 09:30  15
400      2009-02-25 09:30  10

I am trying to get the latest temp for each locId that has updated values within the last 20 min.

So the result I want from the table above would be (say I run the query at 2009-02-25 10:10):

locId    dtg               temp
100      2009-02-25 10:00  15
200      2009-02-25 10:00  20
300      2009-02-25 10:00  24

One more thing that complicates things is that I would like to be able to supply a list on locId that should be selected. I mean use something like "...locId IN (100,200,400)..."

I have tried to use a join on a sub query (as suggested in http://stackoverflow.com/questions/49404/sql-query-to-get-latest-price ) but I can´t get it to work. Not even without the extra "update within the last 20 min".

select t.*
from temperatures as t
 JOIN (select locId, max(dtg) from temperatures where locId IN (100,200,400)  group by locId) as l 
    on l.locId=t.locId and l.dtg=t.dtg
where locId in (100,200,400)

This query gives me SQL error but I cant find error. Is there an error I can´t find or is this way to do it not possible in Informix.

Or is there some other way to go? All help appreciated.

Answer 1

You need to name the max(dtg) column in the subselect - your query just matches all rows by time not just the latest.

select t1.locId, t1.temp, time
   from temperatures t1
      inner join ( select t1.locId, t1.temp, max(t1.dtg) as time
                     from temperatures group by t1.locId, t1.temp) as t2
        on t1.locId = t2.locId
           and t1.dtg = t2.time
    where t1.locId in (100,200,400)

You can add the where condition inside the sub select as well and also you could add a condition to get only the readings in the last 20 minutes.

edit: as per comment - I had typed the wrong join and other errors.

---

Some assistance - the references to t1 in the sub-query are wrong. You need an extra table reference (t3):

select t1.locId, t1.temp, time
   from temperatures t1
        inner join (select t3.locId, t3.temp, max(t3.dtg) as time
                      from temperatures as t3 group by t3.locId, t3.temp) as t2
                        on t1.locId = t2.locId and t1.dtg = t2.time
    where t1.locId in (100,200,400)

This yields the result:

100    15    2009-02-25 10:00
200    20    2009-02-25 10:00
100    13    2009-02-25 09:45
200    18    2009-02-25 09:45
400    12    2009-02-25 09:45
100    11    2009-02-25 09:30
200    15    2009-02-25 09:30
400    10    2009-02-25 09:30

Unfortunately, this is not the required result, though it is getting closer. Part of the trouble is that you don't want t3.temp in the sub-select or its GROUP BY clause.

Answer 2

Your SQL errors can be corrected using the following syntax:

SELECT t.*
FROM temperatures AS t
INNER JOIN (
    SELECT locId, MAX(dtg) AS maxdtg 
    FROM temperatures 
    WHERE locId IN (100,200,400)  GROUP BY locId
) AS l 
ON l.locId = t.locId AND maxdtg = t.dtg
WHERE t.locId IN (100,200,400)

EDIT: Also, one proper and more dynamic way to go about this:

SELECT t2.* FROM (
    SELECT locId, MAX(dtg) AS maxdtg 
    FROM temperatures 
    GROUP BY locId
) t1
INNER JOIN (
    SELECT locId, dtg, temp 
    FROM temperatures
) t2 
ON t2.locId = t1.locId 
    AND t2.dtg = t1.maxdtg
WHERE t2.dtg > CURRENT YEAR TO MINUTE - 20 UNITS MINUTE

EDIT: Was looking for posts more than 20 minutes in the future instead of 20 minutes old... oops!

EDIT AGAIN: Forgot this was for Informix database... gave MSSQL syntax for where clause.

Answer 3

I chose to create a one-row table 'RefDateTime' to hold the reference time (2009-02-25 10:10). There are other ways to handle that - notably writing `DATETIME(2009-02-25 10:10) YEAR TO MINUTE.

CREATE TABLE temperatures
(
    locId   INTEGER NOT NULL,
    dtg     DATETIME YEAR TO MINUTE NOT NULL,
    temp    INTEGER NOT NULL
);

INSERT INTO Temperatures VALUES(100, '2009-02-25 10:00', 15);
INSERT INTO Temperatures VALUES(200, '2009-02-25 10:00', 20);
INSERT INTO Temperatures VALUES(300, '2009-02-25 10:00', 24);
INSERT INTO Temperatures VALUES(100, '2009-02-25 09:45', 13);
INSERT INTO Temperatures VALUES(300, '2009-02-25 09:45', 16);
INSERT INTO Temperatures VALUES(200, '2009-02-25 09:45', 18);
INSERT INTO Temperatures VALUES(400, '2009-02-25 09:45', 12);
INSERT INTO Temperatures VALUES(100, '2009-02-25 09:30', 11);
INSERT INTO Temperatures VALUES(300, '2009-02-25 09:30', 14);
INSERT INTO Temperatures VALUES(200, '2009-02-25 09:30', 15);
INSERT INTO Temperatures VALUES(400, '2009-02-25 09:30', 10);

CREATE TABLE RefDateTime
(
    reftime DATETIME YEAR TO MINUTE NOT NULL
);
INSERT INTO RefDateTime VALUES('2009-02-25 10:10');

SELECT t1.locID, t1.dtg, t1.temp
  FROM temperatures AS t1 JOIN
    (SELECT t2.locID, MAX(t2.dtg) AS latest
        FROM temperatures AS t2
       WHERE t2.dtg > (SELECT RefTime - 20 UNITS MINUTE FROM RefDateTime)
         AND t2.locID IN (100, 200, 400)
       GROUP BY t2.locID) AS t3 ON t1.locID = t3.locID AND t1.dtg = t3.latest
;

This gives what I believe is the correct result:

100     2009-02-25 10:00      15
200     2009-02-25 10:00      20

When the 't2.locID IN (100, 200, 400)' condition is omitted, it also shows the row with the locID of 300 (and temperature of 24).