Complicated Select from Data Dump

[lionkingden]

I have a data dump table (myGPStable) of GPS data records, with fields (unitNo, latitude, longitude, date, time) every one hour the data gets dumped into the table from all the units...

I want to get the latest latitude and longitude of
all the units in a select statement

(Note: the unitno is not a primary key ...as i said this is a dump)

 

[mrdenny]

Something like this should work.

select myGPSTable.unitNo, myGPSTable.latitude, myGPSTable.longitude, myGPSTable.date, myGPSTable.time
from myGPSTable
join (select unitNo, max(convert(datetime, date + ' ' + time) dt from myGPSTable) t on myGPSTable.unitNo = t.unitNo
   and convert(datetime, date + ' ' + time) = t.dt

This should also work (but I like the first one better since it doesn't require the table to be cleared every day at midnight).

This would require the table be cleared nightly.

select myGPSTable.unitNo, myGPSTable.latitude, myGPSTable.longitude, myGPSTable.date, myGPSTable.time
from myGPSTable
join (select unitNo, max(time) dt from myGPSTable) t on myGPSTable.unitNo = t.unitNo
   and time = t.dt

As I said above I like the first one better, but the second one will have better performance.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[lionkingden]

Thanks a lot Denny... u pointed me in the right direction...

select unitno,latitude,longitude, Max(date||digits(time)) as dt from myGPSTable group by unitno

u know the above code wont work in DB2 because with group by clause i cant put the fields latitude and longitude as they are neither in the group by or aggregation clause

but this below code gave me the right recods without the latitude and longitude

select unitno, Max(date||digits(time)) as dt from myGPSTable group by unitno

I should have mentioned that i am using db2 database on as400....
so some of the things u mentioned does Not work...but i got the LOGIC , thanks a lot for that..
..but with about 40 million records this was damm innefficient....

the date in my table was in the format yyyyddd eg: 2005137 year and day of year

time in a numeric format hhmmss ...had to use digits to convert it to fixed format string with no zeros trucated...

|| is the stringconcatanating operator in DB2

What say u....?
anyway to make it efficient....

 

[mrdenny]

I'm no DB2 DBA.

How do your indexes look in this table? Where's your clustered index (if you have one)?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)