2 Replies Latest reply on Oct 9, 2014 3:23 PM by edoarsla

    Query for the last entries before specific revision

    edoarsla

      Hi,

       

      I am having difficulties writing a query that will return a list of Entity(s) that are:

      - less than a specific Revision Number;

      - exclude any Entity(s) that have been deleted;

      - and are the latest revision of each ID.

       

      So, if this is the Audit Table

       

      id

      REV

      REVTYPE

      REVEND

      active

      value

      1

      1

      0

      6

      1

      will be deleted

      1

      6

      2

      NULL

      NULL

      NULL

      2

      2

      0

      4

      1

      property1-0

      2

      4

      1

      7

      1

      property1-1

      2

      7

      1

      9

      1

      property1-2

      2

      9

      1

      11

      1

      property1-3

      2

      11

      2

      NULL

      NULL

      NULL

      3

      3

      0

      5

      1

      property2-0

      3

      5

      1

      8

      1

      property2-1

      3

      8

      1

      10

      1

      property2-2

      3

      10

      1

      11

      1

      property2-3

      3

      11

      2

      NULL

      NULL

      NULL

       

       

      A Query for changes that happened before/or on REV 8 should return:

       

      id

      REV

      REVTYPE

      REVEND

      active

      value

      2

      7

      1

      9

      1

      property1-2

      3

      8

      1

      10

      1

      property2-2

       

       

      Any pointers would be greatly appreciated.

       

      Thank you!