finding the last date

[notstock78]

In my database I have a new entry entered automatically every so often. The entry does not have a specific time that it enters but the time it is entered is kept track of. I am trying to pull the most recent entry to the database using MSSQL but I cannot get it correct. I have pulled all the entries for the last hour and even down to the last 1/2 hour but this will still not pull all the records that I am looking for because some times it takes 3 hours to get one entry and sometimes I will get 6 in 1 hour.

this table keeps track of different positions of our vehicles with satalite positioning. We have many different vehicles with each there own ID. There is another column with the date and time when the truck was pinged. This is what I have so far.

select POSHIST.processed, POSHIST.tmwin_loc, MOBILE_DEVICE.ASSIGNED_TO
from POSHIST, MOBILE_DEVICE
where POSHIST.DEVICE=MOBILE_DEVICE.DEVICE_ID
AND PROCESSED BETWEEN CURRENT TIMESTAMP - 1 hours AND
CURRENT TIMESTAMP + .25 hours
ORDER BY ASSIGNED_TO


I am sure you already know that the PROCESSED is the date and time stamp that the truck is pinged and the MOBILE_DEVICE.ASSIGNED_TO is the truck ID.

 

[SQLDenis]

If you need just 1 record back use order by processed DESC

if you need more than 1 record,group all the fields except for processed and use MAX(processed)

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[notstock78]

I am very close to getting what I want. I have this so far but I still want one more field in this quiery. I am still trying to put the field POSHIST.TMWIN_LOC. When I try to put more that 2 fields I get an error, what am I doing wrong? Here is what I've got so far.

SELECT MOBILE_DEVICE.ASSIGNED_TO, MAX(POSHIST.PROCESSED) AS LAST_UPDATE
FROM POSHIST, MOBILE_DEVICE
WHERE POSHIST.DEVICE=MOBILE_DEVICE.DEVICE_ID
GROUP BY MOBILE_DEVICE.ASSIGNED_TO