CODEWITHSUNDEEP
t-sql
Anh Bảy
Anh Bảy

Reputation: 738

SQL find out decrease in increase sequence

I have the following table structure:

  Seq  Value
  1    100
  2    200
  3    199
  4    200
  5    201
  6    300
  7    299

I want to find Seq number 3 and 7 which decreased value from previous Seq order

I found this solution but seem not right for me T-SQL to determine "out of sequence" records

Create table test (Seq int, Value int);
INSERT INTO test VALUES (1,100), (2,200), (3,199), (4,200), (5,201), (6,300), (7,299);
SELECT
  Seq
FROM
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY Seq ORDER BY Seq) As sequenceCorrect,
    ROW_NUMBER() OVER (PARTITION BY Seq ORDER BY Value, Seq) as sequenceActual
  FROM
    test
)
  AS Checker
WHERE
  sequenceCorrect <> sequenceActual
ORDER BY
  Seq

Thanks HABO for LEAD/LAG suggestion

SELECT
  Seq
FROM
(
  SELECT
    *,
    LAG(Value) OVER (ORDER BY Seq) as Pre
  FROM
    test
)
  AS Checker
WHERE
  Pre > Value
ORDER BY
  Seq

Upvotes: 0

Views: 148

Answers (1)

Josh
Josh

Reputation: 21

select a.seq from test a
join test b on a.seq-1=b.seq
where a.value<b.value

Upvotes: 1

Related Questions