Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Select first rows from subselect
Message
From
06/10/2015 00:50:34
 
 
To
05/10/2015 20:46:42
General information
Forum:
Microsoft SQL Server
Category:
Replication
Environment versions
SQL Server:
SQL Server 2014
Application:
Web
Miscellaneous
Thread ID:
01625508
Message ID:
01625513
Views:
71
This message has been marked as the solution to the initial question of the thread.
OK, I created two tables with similar names and columns, but not identical (I don't like using keywords as column names)

So I did this...
CREATE TABLE dbo.Models    (Code varchar(10), Name varchar(20), IdCompany int)
create table dbo.Testdata ( Testdate date, testhour time,    model varchar(10),         temperature   decimal(10,2),      energy int)
Then I populated the rows using your test data.

And then I wrote this query. This was a first shot. There are several ways this could be done. Some people would use a CROSS APPLY for this. I use a common table expression to get the max datetime for each model, and then joined it "back" to the source table to get the other columns. . Again, several ways it could be done.

This generated the results. Now, I'm assuming that the values of date and time would be unique for a specific model. If you had multiple temperature readings for the exact same date and time, my query would bring back both rows. If you truly needed just 1, you'd have to use a CROSS APPLY with a SELECT TOP 1 (or some other similar pattern). But if you'd want both temperature readings back for the exact same date/time (assuming that's even possible), then this query would work.

One other thing. To get a single scalar value for the most recent row, I temporarily concatenated the data and time into a datetime....and then stripped them back out when joining back to the original table.

So there are probably opportunities to optimize this - but I think this should work.
  ;WITH MaxDateCTE as
     -- get the most recent datetime value for each model...concatenate the separate data and time values into one value
     (SELECT Model,   MAX(  CONVERT(DATETIME, CONVERT(CHAR(8), testdate, 112)   + ' ' + CONVERT(CHAR(8), testhour, 108)) ) AS MaxDate
	    FROM TestData  GROUP BY Model)

SELECT Models.*, TestData.Testdate, TestData.testhour, TestData.temperature, TestData.energy
FROM Models
   JOIN MaxDateCTE ON Models.Code = MaxDateCTE.model 
    JOIN Testdata ON CAST(MaxDateCTE.MaxDate as DATE) =  TestData.Testdate            -- now join "back"
	                         AND CAST(MaxDateCTE.MaxDate AS TIME) = TestData.testhour     -- and strip out the date/time when reading back to original data
							 AND MaxDateCTE.model = Testdata.model
  	    where Models.IdCompany = 10
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform