Get Latest Data From Sql For

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]

Solution 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
  groupby 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
        andmax(a.Temp_Date) = t.Temp_Date) Temperature
  from t a
  groupby Building_ID,Sector_ID
  havingmax(Temp_Date) =max(Temp_Date)

Solution 2:

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

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