Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
patbe
Starting Member
9 Posts |
 Posted - 2005-03-14 : 07:29:59
|
| I have this query:SELECT dbo.Pejl.PejlID AS ID, CustomerName AS Customer, MeasurementObjectName AS 'Measure Object', ProductName AS Product, MeasurementObjectTypeName AS 'Object Type', MeasurementLogValue AS 'Measured Value', MeasurementLogTimeStamp AS Time, MeasurementLogTemperature AS Temperature FROM dbo.Pejl LEFT JOIN dbo.Customer ON dbo.Pejl.CustomerID = dbo.Customer.CustomerID LEFT JOIN dbo.MeasurementObject ON dbo.Pejl.PejlID = dbo.MeasurementObject.PejlID LEFT JOIN dbo.Product ON dbo.MeasurementObject.ProductID = dbo.Product.ProductID LEFT JOIN dbo.MeasurementObjectType ON dbo.MeasurementObject.MeasurementObjectTypeID = dbo.MeasurementObjectType.MeasurementObjectTypeID LEFT JOIN dbo.MeasurementLog ON dbo.MeasurementObject.MeasurementObjectID = dbo.MeasurementLog.MeasurementObjectIDnow I want to get only the latest(MeasurementLogTimeStamp) value for each PejlIDHow do I do thsi best?.p |
|
|
patbe
Starting Member
9 Posts |
Posted - 2005-03-14 : 09:01:55
|
| solved it for intressted here it is:SELECT pe.PejlID AS ID , c.CustomerName AS Customer , mo.MeasurementObjectName AS 'Measure Object' , pr.ProductName AS Product , mot.MeasurementObjectTypeName AS 'Object Type' , ml.MeasurementLogValue AS 'Measured Value' , ml.MeasurementLogTimeStamp AS Time , ml.MeasurementLogTemperature AS Temperature FROM dbo.Pejl pe LEFT JOIN dbo.Customer c ON pe.CustomerID = c.CustomerID LEFT JOIN dbo.MeasurementObject mo ON pe.PejlID = mo.PejlID LEFT JOIN dbo.Product pr ON mo.ProductID = pr.ProductID LEFT JOIN dbo.MeasurementObjectType mot ON mo.MeasurementObjectTypeID = mo.MeasurementObjectTypeID LEFT JOIN ( SELECT MeasureMentObjectId, MAX(MeasurementLogTimeStamp) AS MeasurementLogTimeStamp FROM dbo.MeasurementLog GROUP BY MeasureMentObjectId ) mlMax ON mo.MeasurementObjectID = mlMax.MeasurementObjectID LEFT JOIN dbo.MeasurementLog ml ON mo.MeasurementObjectID = ml.MeasurementObjectID AND mlMax.MeasurementLogTimeStamp = ml.MeasurementLogTimeStamp |
 |
|
|
|
|
|
|
|
query help