Get Latest Data from SQL for

Question

I have a question Simmilar to this one except I have a table that looks like this:

Temp_Date             Building_ID          Sector_ID          Temperature    
[Date/Time]           [I32]                [I32]              [DBL]
1/9/2018 4:14:31 AM    456                   0                20.23    
1/9/2018 4:15:14 AM    123                   1                35.23    
1/9/2018 4:16:21 AM    123                   0                15.23    
1/9/2018 4:15:45 AM    123                   2                25.23    
1/9/2018 4:16:21 AM    456                   0                25.23    
1/9/2018 4:16:59 AM    123                   1                35.23

I would like to Get a result data for the latest logged temperature for each unique building/sector combination.

For the example dataset, the table I'm looking for would look like

Building_ID          Sector_ID          Temperature
123                    0                15.23
123                    1                35.23
123                    2                25.23
456                    0                25.23

From what I understand, the code should look something like:

select t.Building_ID, t.Sector_ID, t.Temperature, t.Temp_Date
from MyTable t
inner join (
    select Building_ID, Sector_ID, max(Temp_Date) as MaxTemp_Date
    from MyTable
    group by Building_ID
) tm on t.Building_ID = tm.Building_ID and t.Sector_ID = tm.Sector_ID and t.Temp_Date = tm.Temp_Date

EDIT

Came back to it this morning and I believe the following code is getting me what I want

select t.Building_ID, t.Sector_ID, t.Temperature, t.Temp_Date
from MyTable t
inner join (
    select Building_ID, Sector_ID, max(date_time) as maxMaxTemp_Date
    from MyTable t
    group by Building_ID, Sector_ID
    ) tm on t.Building_ID = tm.Building_ID and t.Sector_ID = tm.Sector_ID and t.Temp_Date=tm.MaxTemp_Date
ORDER BY t.Building_ID, t.Sector_ID

You are missing Section_ID in the `group by` clause of the inner query. — kc2018, Jan 11 '18 at 23:55
@kc2018 This was my problem if you make this a full answer, I'll accept it as the solution — ATE-ENGE, Jan 12 '18 at 18:33

tonypdmtr · Accepted Answer · 2018-01-12T19:40:28.227

1

(Depending on your database you may need to change the syntax a bit.)

This one works for SQLite3:

select Building_ID,Sector_ID,Temperature,Temp_Date
  from t
  group by Building_ID,Sector_ID having max(Temp_Date);

For MySQL, SQL Server, and PostgreSQL that are stricter with having syntax, something like the following:

select Building_ID,Sector_ID,(
    select Temperature
      from t
      where a.Building_ID = t.Building_ID
        and a.Sector_ID = t.Sector_ID
        and max(a.Temp_Date) = t.Temp_Date) Temperature
  from t a
  group by Building_ID,Sector_ID
  having max(Temp_Date) = max(Temp_Date)

edited Jan 12 '18 at 19:40

answered Jan 12 '18 at 13:09

tonypdmtr

3,037
2
17
29

Although I want the latest temperature value not the highest temperature, shouldn't it be `having max(t.Temp_Date)`? – ATE-ENGE Jan 12 '18 at 17:34

score 0 · Answer 2 · answered Jan 11 '18 at 23:54

In most databases, you would use the ANSI standard window function row_number():

select t.*
from (select t.*,
             row_number() over (partition by building_id, sector_id order by temp_date desc) as seqnum
      from mytable t
     ) t
where seqnum = 1;

Get Latest Data from SQL for

2 Answers2

Linked