CODEWITHSUNDEEP
sql-server
pithhelmet
pithhelmet

Reputation: 2282

Eliminating 'duplicate' rows in status table

I have a status table that is updated once every five seconds. most of the data will remain the same (lat/lng) since the vehicle will be stationary most of the time.

without taking the route of reading each record, and comparing it to the previous one.... is there a way to skip the records that are exactly the same (except the update datetime field)?

Upvotes: 0

Views: 35

Answers (2)

GroovySu
GroovySu

Reputation: 109

I would tackle this at the insert stage, by comparing what you're about to insert with the most recent record, only one line at a time.

But if you can't do that, one approach might be to extract just the earliest record for each lat and long, using a nested query. I don't like this very much because it's horribly inefficient, but might give someone cleverer than me an idea. (This example assumes that you're tracking vehicles and each vehicle has a code).

It will give you the earliest date/time that vehiclecode was at that lat and long. Could be a problem if a vehicle goes over the same ground twice, unless you do something funky with the sub query, like make it 'earliest it was at that lat/long one day', or in one hour, or something.

SELECT
    a.AnyOtherFieldsYouMightWantFromTheTrackerTable,
    a.VehicleID,
    a.Lat,
    a.Long,
    a.Date
FROM tblTracker as a
JOIN (
    SELECT
       VehicleID,
       Lat, -- you don't really need Lat or Long in here but 
       Long, -- they're useful for running your subquery on its own
       Min(DateTime) as MinDateTime
    FROM tblTracker
    GROUP BY VehicleID, Lat, Long -- so long as they're in here
    ) as b
ON a.VehicleID = b.VehicleID
AND a.DateTime = b.MinDateTime

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460048

You can use a CTE + ROW_NUMBER:

WITH CTE AS
(
    SELECT ID, lat, lng, DateTimeColumn,
           RN = ROW_NUMBER() OVER (PARTITION BY lat, ln
                                   ORDER BY DateTimeColumn ASC)
    FROM dbo.TableName
)
SELECT ID, lat, ln, DateTimeColumn
FROM CTE
WHERE RN = 1

This returns only the first row of each group of data with the same lat, ln values.

Upvotes: 2

Related Questions