I'm having this issue Now, I'm having this table

Then, I want to return 2 random number (these 2 number is from 00 to 99) which will be taken from front-end. Lets call them Random1 and Random2. Now, when the query running, it will run from record 1 --> final

If whenever the FirstNo = Random1, then the next record will take the condition of FirstNo = Random2. Then, at the next record, it will return to get the condition of FirstNo = Random1.

For example, Random1 = 56, Random2 = 91, and the table as above

First record: FirstNo != 56, move to the next row.

Second record: FirstNo = 56.

Third record: (Because of at the previous record, FirstNo equal Random1 (56), this record will get Random2 instead) FirstNo = 91.

Fourth record: Back to Random1, .....

So, I'm figuring out something like:

SELECT * FROM CODE_DEX WHERE FirstNo = @Random1
SELECT * FROM CODE_DEX WHERE FirstNo = @Random2

I haven't have any idea how to joining these 2. I can create a column ISTRUE or something to Declare and Set in SQL, but I don't think I can use IF in the middle of the sql query.

Thank you so much

CodePudding user response：

Based on stated requirement

If the prior row value for FirstNo ordered by date is Random1 then return random2 for FirstNo

You would do that like this in SQL

SELECT Code, 
       CASE WHEN LAG(FirstNo,1) OVER (ORDER BY Date_Input) = Random1 
            THEN Random2
            ELSE FirstNo END AS FirstNo,
       LastNo,
       Date_input
FROM sometableyoudidnotname
ORDER BY Date_input

   

CodePudding user response：

You can select:

• next value, with the LEAD window function
• previous value, with the LAG window function

then filter out all rows which don't have "Random1" and "Random2" values in consecutive rows:

SET @Random1 = 56;
SET @Random2 = 91;

WITH cte AS (
       SELECT *,
              LAG(FirstNo)  OVER(ORDER BY Code, Date_input) AS PrevNo,
              LEAD(FirstNo) OVER(ORDER BY Code, Date_input) AS NextNo
       FROM tab
)
SELECT Code,
       FirstNo,
       LastNo,
       Date_input
FROM cte 
WHERE (FirstNo = @Random1 AND NextNo = @Random2)
   OR (FirstNo = @Random2 AND PrevNo = @Random1)

Check the demo here.