Post reply

Query assistance please: fetching repeated values

  • November 20, 2013 at 11:18 am

    #395868

    Good morning,

    I've tried Googling this but not sure what to ask for. We have a data table that is filled by an external machine monitoring machine. I am trying to find the first occurrence of a 'Value' in the set, but the Value may repeats because they wrap.

    The raw data looks like this.

    Id DateTime 'Value'

    111/20/13 13:1010

    211/21/13 13:1010

    311/22/13 13:1010

    411/23/13 13:1011

    511/24/13 13:1011

    611/25/13 13:1012

    711/26/13 13:1012

    811/27/13 13:1012

    911/28/13 13:101

    1011/29/13 13:101

    1111/30/13 13:102

    1212/01/13 13:102

    1312/02/13 13:103

    1412/03/13 13:1010

    1512/04/13 13:1010

    The output I would like to achieve is this

    111/20/13 13:1010

    411/23/13 13:1011

    611/25/13 13:1012

    911/28/13 13:101

    1111/30/13 13:102

    1312/02/13 13:103

    1412/03/13 13:1010

    .

    I suppose I could use a loop but that is how I would program it with c# or something like that.

    .

    DECLARE @t TABLE

    (ID INT IDENTITY(1,1),

    DateValue datetime,

    Value INT

    )

    INSERT INTO @t (DateValue, Value) VALUES ('11/20/13 13:10', 10)

    INSERT INTO @t (DateValue, Value) VALUES ('11/21/13 13:10', 10)

    INSERT INTO @t (DateValue, Value) VALUES ('11/22/13 13:10', 10)

    INSERT INTO @t (DateValue, Value) VALUES ('11/23/13 13:10', 11)

    INSERT INTO @t (DateValue, Value) VALUES ('11/24/13 13:10', 11)

    INSERT INTO @t (DateValue, Value) VALUES ('11/25/13 13:10', 12)

    INSERT INTO @t (DateValue, Value) VALUES ('11/26/13 13:10', 12)

    INSERT INTO @t (DateValue, Value) VALUES ('11/27/13 13:10', 12)

    INSERT INTO @t (DateValue, Value) VALUES ('11/28/13 13:10', 1)

    INSERT INTO @t (DateValue, Value) VALUES ('11/29/13 13:10', 1)

    INSERT INTO @t (DateValue, Value) VALUES ('11/30/13 13:10', 2)

    INSERT INTO @t (DateValue, Value) VALUES ('12/1/13 13:10', 2)

    INSERT INTO @t (DateValue, Value) VALUES ('12/2/13 13:10', 3)

    INSERT INTO @t (DateValue, Value) VALUES ('12/3/13 13:10', 10)

    INSERT INTO @t (DateValue, Value) VALUES ('12/4/13 13:10', 10)

    Thank you for your help.

  • November 20, 2013 at 11:29 am

    #1667886

    Here's an option. If your ID has gaps, you should create a virtual one using ROW_NUMBER()

    SELECT t1.*

    FROM @t t1

    LEFT

    JOIN @t t2 ON t1.ID = t2.ID + 1 AND t1.Value = t2.Value

    WHERE t2.ID IS NULL

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 20, 2013 at 11:42 am

    #1667890

    Thank you. That was perfect.

Viewing 3 posts - 1 through 2 (of 2 total)

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