sql distic query



  • There is a tbl. there is a service on the other side, which adds a new record at some time interval.

    Example:

    3 fields varA varchar, intB integer, timeC

    5 records

    'value', 1, 19-00
    'value', 2, 19-01
    'value', 2, 19-02
    'value', 3, 19-03
    'value', 2, 19-04

    Only records where the meaning of the second field has changed, including the value that has ever occurred, are required.

    The example is:

    First record

    In the second record, the value changed from one to two, it's got to get to the cutts.

    The third record didn't change the meaning, she's not supposed to get in the slice.

    The fourth record changed from 2 to 3 to cuts.

    5 changed from 3 backwards to 2 (2 earlier), under the terms of the target, this DAWNA entry into the slice.

    Total received that resultset should consist of 4 records

    'value', 1, 19-00
    'value', 2, 19-01
    'value', 3, 19-03
    'value', 2, 19-04

    I can only come up with this trainer, but I'd like a spleen. I don't like cadets, I think they're slow.



  • For MS SQL Server:

    SELECT t.* FROM tbl t
    OUTER APPLY (SELECT TOP 1 intB FROM #tbl t2 WHERE t2.timeC<t.timeC ORDER BY t2.timeC desc) flt
    WHERE flt.intB<>t.intB OR flt.intB IS null
    

    For each record, we take the previous time if the value has changed or there is no previous record (first entry) - we will.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2