SQL Server Select Statement Help

[chadbrown]

Ok I can do this so simply in mysql but sql server is a different animal. What I am tring to do is go and get the last record for each "devid" the last record would be the newest record using the timestamp field.

SELECT
dbo.DevInfo.DevId,
dbo.DevInfo.DevName,
dbo.DevInfo.PhoneNumber,
dbo.DevStatLocation.[TimeStamp],
dbo.DevStatLocation.Latitude,
dbo.DevStatLocation.Longitude,
(DATEADD(HOUR,-4,dbo.DevStatLocation.[TimeStamp])) AS est,
dbo.cwc_device_logid.logid,
dbo.cwc_groups.GrpName,
dbo.DevInfo.Online,
dbo.DevInfo.LastConnTime,
dbo.DevInfo.BatteryStatus,
dbo.DevStatLocation.Heading,
dbo.DevInfo.DevName AS unbound1,
dbo.DevInfo.DevName AS unbound2,
dbo.DevInfo.DevName AS unbound3,
dbo.DevInfo.DevName AS unbound4,
(dbo.DevStatLocation.Speed)/.62137 AS km
FROM
dbo.DevInfo
LEFT JOIN dbo.DevStatLocation ON dbo.DevInfo.DevId = dbo.DevStatLocation.DevId
INNER JOIN dbo.cwc_device_logid ON dbo.cwc_device_logid.DevId = dbo.DevInfo.DevId
INNER JOIN dbo.cwc_groups ON dbo.cwc_groups.logid = dbo.cwc_device_logid.logid
WHERE
dbo.DevStatLocation.[TimeStamp] IS NOT NULL

Thanks in advance.

[glenschild]

Re: SQL Server Select Statement Help

SELECT TOP 1
..
..
..


ORDER BY dbo.dEvStatLocation.[TIMESTAMP] DESC


Should do the trick

Ok I can do this so simply in mysql but sql server is a different animal. What I am tring to do is go and get the last record for each "devid" the last record would be the newest record using the timestamp field.

SELECT
dbo.DevInfo.DevId,
dbo.DevInfo.DevName,
dbo.DevInfo.PhoneNumber,
dbo.DevStatLocation.[TimeStamp],
dbo.DevStatLocation.Latitude,
dbo.DevStatLocation.Longitude,
(DATEADD(HOUR,-4,dbo.DevStatLocation.[TimeStamp])) AS est,
dbo.cwc_device_logid.logid,
dbo.cwc_groups.GrpName,
dbo.DevInfo.Online,
dbo.DevInfo.LastConnTime,
dbo.DevInfo.BatteryStatus,
dbo.DevStatLocation.Heading,
dbo.DevInfo.DevName AS unbound1,
dbo.DevInfo.DevName AS unbound2,
dbo.DevInfo.DevName AS unbound3,
dbo.DevInfo.DevName AS unbound4,
(dbo.DevStatLocation.Speed)/.62137 AS km
FROM
dbo.DevInfo
LEFT JOIN dbo.DevStatLocation ON dbo.DevInfo.DevId = dbo.DevStatLocation.DevId
INNER JOIN dbo.cwc_device_logid ON dbo.cwc_device_logid.DevId = dbo.DevInfo.DevId
INNER JOIN dbo.cwc_groups ON dbo.cwc_groups.logid = dbo.cwc_device_logid.logid
WHERE
dbo.DevStatLocation.[TimeStamp] IS NOT NULL

Thanks in advance.

[chadbrown]

Re: SQL Server Select Statement Help

Thanks Glen thats a no go all that gives me is the last entry of the whole table by timestamp I need to get one for each DevId

[glenschild]

Re: SQL Server Select Statement Help

Okay so misunderstood! If I now understand correctly you want to find the latest instance of each devid?

So by the sounds of it need to first group the records into devid groups


So

SELECT
devid,
max(devstatLocation.[timestamp] AS xxxxx,

FROM
..
..

GROUP BY devid

The challenge with the group by statement is that you will need to wrap every field inside a max() statement or similar except for the grouping fields

[chadbrown]

Re: SQL Server Select Statement Help

Here is the grouping view then I built a seperate join view to tie it all together. Thanks Glen


SELECT c1.*
FROM DevStatLocation c1
INNER JOIN ( SELECT DevStatLocation.DevId,
max([TIMESTAMP]) as max_timestamp
FROM DevStatLocation
GROUP BY DevId ) c2
ON c1.DevId = c2.DevId
AND c1.[TimeStamp] = c2.max_timestamp

[glenschild]

Re: SQL Server Select Statement Help

Glad it worked. I must admit I started with mysql but out of necessity had to work on an MS SQL installation and now all new work is in MS SQL.

[chadbrown]

Re: SQL Server Select Statement Help

Seems to be way more coding to me than mysql