Select most recent record

  • Every hour we capture some values from our factory (position of pumps, valves, ...) in our sql server 2000 db.

    Normally 1 record is added to the db.

    00:00:00

    01:00:00

    02:00:00

    ...

    21:00:00

    22:00:00

    23:00:00

    All of these values are displayed in an Excel sheet. One sheet contains all the data from one month.

    I noticed a problem last week when 2 records were added: first one at 19:00:00 and the second one at 19:00:01

    We only want to keep the most recent record (19:00:01) in a situation like this but I can't seem to work out an SQL-statement

    This is what we have know. It used to work fine untill we had 2 record being added instead of one.


    SELECT     TimeOfCapture, Value1, Value2, Value3

    FROM         FactoryTable

    WHERE     (MONTH(TimeOfCapture) = MONTH(GETDATE())) AND (YEAR(TimeOfCapture) = YEAR(GETDATE()))


  • What are the circumstances under which you wish to discard the less recent value?  Is it when it is less than an hour before the previous one?  Or is it when some combination of Value1, Value2 and Value3 is not unique?

    John

  • I only want to see 24 values each day.

    In normal circumstances this is not a problem as the db normally receives 1 new record each hour.

    We have no idea why suddenly 2 record were being added one second after the other as this has never happened before and there is nothing in the logfile indicating there was a problem of some sort.

    Combinations of values don't have to be unique.

    These are the times when records got added on the 14th.

    As you can see there is a difference of 1 second between 12:06:13 and 13:06:12 but that doesn't matter.

    Problem is indicated in red: we only want to keep the values captured at 19:06:13 because we noticed the values captured at 19:06:12 seem to be just copies of the ones captured at 18:06:12. The actual new values are in the "19:06:13"-record.

    14/12/2006 0:06:13

    14/12/2006 1:06:13

    14/12/2006 2:06:13

    14/12/2006 3:06:13

    14/12/2006 4:06:13

    14/12/2006 5:06:13

    14/12/2006 6:06:13

    14/12/2006 7:06:13

    14/12/2006 8:06:13

    14/12/2006 9:06:13

    14/12/2006 10:06:13

    14/12/2006 11:06:13

    14/12/2006 12:06:13

    14/12/2006 13:06:12

    14/12/2006 14:06:12

    14/12/2006 15:06:12

    14/12/2006 16:06:12

    14/12/2006 17:06:12

    14/12/2006 18:06:12

    14/12/2006 19:06:12

    14/12/2006 19:06:13

    14/12/2006 20:06:12

    14/12/2006 21:06:12

    14/12/2006 22:06:12

    14/12/2006 23:06:12

    So when we have more than one record being added in - let's say - 10 minutes time, we want to keep the most recent value.

     

  • Probably what you need, then, is to create a trigger in the table that checks the time of the previous insert, and if it was less than ten minutes ago, deletes the previous row.

    John

  • try this, it won't delete the data but should display what you are looking for

     

    SELECT    TimeOfCapture, Value1, Value2, Value3) FROM         FactoryTable

    join (select max(timeofcapture) as maxtoc from factorytable group by convert(varchar,timeofcapture,101),datepart(hh,timeofcapture)

    WHERE     (MONTH(TimeOfCapture) = MONTH(GETDATE())) AND (YEAR(TimeOfCapture) = YEAR(GETDATE())) ) mtoc on timeofcapture = maxtoc


  • With my workload winding down for Christmas (lucky me!! ) I've put this together as an example trigger that you could use:

     

    CREATE TABLE tmpSolution(

     tTime datetime)

    GO

    CREATE TRIGGER tmpInsSolution

    ON tmpSolution

    FOR INSERT

    AS

    BEGIN

     DECLARE @tMaxTime datetime

     DECLARE @tNewTime datetime

     SELECT @tNewTime = tTime FROM inserted

     SELECT @tMaxTime = MAX(tTime) FROM tmpSolution WHERE tTime < @tNewTime

     

     IF DATEDIFF(mi, @tMaxTime, @tNewTime) IS NOT NULL AND DATEDIFF(mi, @tMaxTime, @tNewTime) < 10

      DELETE FROM tmpSolution WHERE tTime = @tMaxTime

    END

    GO

    -- Normal operation

    INSERT INTO tmpSolution VALUES( '2006/12/25 00:00:00')

    INSERT INTO tmpSolution VALUES( '2006/12/25 01:00:00')

    INSERT INTO tmpSolution VALUES( '2006/12/25 02:00:00')

    INSERT INTO tmpSolution VALUES( '2006/12/25 03:00:00')

    INSERT INTO tmpSolution VALUES( '2006/12/25 04:00:00')

    INSERT INTO tmpSolution VALUES( '2006/12/25 05:00:00')

    INSERT INTO tmpSolution VALUES( '2006/12/25 06:00:00')

    SELECT * FROM tmpSolution

    GO

    -- Second record within 10 minutes

    INSERT INTO tmpSolution VALUES( '2006/12/25 06:06:13')

    SELECT * FROM tmpSolution

    GO

    -- Resume normal operation

    INSERT INTO tmpSolution VALUES( '2006/12/25 07:00:00')

    INSERT INTO tmpSolution VALUES( '2006/12/25 08:00:00')

    SELECT * FROM tmpSolution

    GO

    DROP TABLE tmpSolution

    GO

    Hope this helps!



    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • Use a self join. A self join is when you join a table to itself.

    To see all of the rows considered duplicates:

    Select

    Copy1.*

    From

    MyTable As Copy1

    Inner

    Join MyTable As Copy2 On DateDiff(minute, Copy1.MyDateField, Copy2.MyDateField) < 55 And DateDiff(hour, Copy1.MyDateField, Copy2.MyDateField) = 0

    To see all of the rows that will be deleted:

    Select Copy1.*

    From

    MyTable As Copy1

    Inner

    Join MyTable As Copy2 On DateDiff(minute, Copy1.MyDateField, Copy2.MyDateField) < 55 And DateDiff(hour, Copy1.MyDateField, Copy2.MyDateField) = 0

    Where

    Copy1.MyDateField < Copy2.MyDateField

    To delete the rows:

    Delete

    Copy1

    From

    MyTable As Copy1

    Inner

    Join MyTable As Copy2 On DateDiff(minute, Copy1.MyDateField, Copy2.MyDateField) < 55 And DateDiff(hour, Copy1.MyDateField, Copy2.MyDateField) = 0

    Where

    Copy1.MyDateField < Copy2.MyDateField


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply