SQL problem with checking next and previous rows I have a list and the returned table looks like this. I took the preview of only one car but there are many more. 



What I need to do now is check that the current KM value is larger then the previous and smaller then the next. If this is not the case I need to make a field called Trustworthy and should fill it with either 1 or 0 (true/ false). 

The result that I have so far is this:



validKMstand and validkmstand2 are how I calculate it. It did not work in one list so that is why I separated it. 

In both of my tries my code does not work. 

15756 should be 0 in validkmstand2 as it is larger than the next value.
15757 should be 0 in validKMstand as it is smaller than the previous value

Here is the code that I have so far. 

 FullList as (
SELECT
    *
FROM
eMK_Mileage as Mileage
)


, ValidChecked1 as (
SELECT 
        UL1.*,
        CASE WHEN EXISTS(
            SELECT TOP(1)UL2.* 
            FROM FullList AS UL2
            WHERE
                UL2.FK_CarID = UL1.FK_CarID AND
                UL1.KM_Date &gt; UL2.KM_Date AND
                UL1.KM &gt; UL2.KM
            ORDER BY UL2.KM_Date DESC
        )
        THEN 1
        ELSE 0
        END AS validkmstand
FROM FullList as UL1
)

, ValidChecked2 as (
SELECT 
        List1.*,
        (CASE   WHEN List1.KM &gt; ulprev.KM
                THEN 1
                ELSE 0
                END
        ) AS validkmstand2
FROM ValidChecked1 as List1 outer apply
    (SELECT TOP(1)UL3.* 
            FROM ValidChecked1 AS UL3
            WHERE
                UL3.FK_CarID = List1.FK_CarID AND
                UL3.KM_Date &lt;= List1.KM_Date AND
                List1.KM &gt; UL3.KM
            ORDER BY UL3.KM_Date DESC) ulprev
)

SELECT * FROM ValidChecked2 order by FK_CarID, KM_Date
How about a rewrite of it?

;with eMK_Mileage
AS (SELECT 15755 AS ID, '8E79F' AS FK_CarID, 69646 AS KM , '20140716' AS KM_Date UNION ALL
SELECT 15756,'8E79F' , 70393 , '20140726' UNION ALL
SELECT 15757,'8E79F' , 58258 , '20140822' UNION ALL
SELECT 15758,'8E79F' , 72055 , '20140822')
,FullList 
AS (
 SELECT  * FROM eMK_Mileage as Mileage
)
-- FINAL SELECT
SELECT
*
,CASE WHEN FL.KM &lt; ISNULL(P.KM_PREV,FL.KM) THEN 0 ELSE 1 END AS validKMstand
,CASE WHEN FL.KM &gt; ISNULL(N.KM_NEXT,FL.KM) THEN 0 ELSE 1 END AS validKMstand2   
FROM 
FullList AS FL
OUTER APPLY 
--PREVIOUS ROW
(SELECT TOP 1 
        KM AS KM_PREV
    FROM FullList AS PREV
    WHERE
        FL.FK_CarID = PREV.FK_CarID
        AND FL.KM_Date&gt;=PREV.KM_Date
        AND FL.ID&gt;PREV.ID
    ORDER BY 
        KM_Date DESC, ID DESC
)AS P
--NEXT ROW
OUTER APPLY 
(SELECT TOP 1 
        KM AS KM_NEXT
    FROM FullList AS NXT
    WHERE
        NXT.FK_CarID = FL.FK_CarID
        AND NXT.KM_Date&gt;=FL.KM_Date
        AND NXT.ID &gt; FL.ID
    ORDER BY 
        KM_Date ASC, ID ASC
)AS N
ORDER BY ID,KM_Date


the output, for my sample is this:

ID  FK_CarID    KM  KM_Date KM_PREV KM_NEXT validKMstand    validKMstand2
15755   8E79F   69646   20140716    NULL    70393   1   1
15756   8E79F   70393   20140726    69646   58258   1   0
15757   8E79F   58258   20140822    70393   72055   0   1
15758   8E79F   72055   20140822    58258   NULL    1   1