Compare 2 sets of columns in same table

Question

hi i have a table like that id value id value ------------- ------------------ 1 2 1 2 2 4 (or) 2 4 3 3 3 6 now i want a query. that id is descendi order the values mustbe in descending order then only return true. else return false. i mean 1st tbl return false 2nd tbl return true pls help me

Tapan Patel · Answer

Correct me if I am wrong. What you are looking for is matching the sequential values in a table(s). Considering you will always have a unique id column and data are sequential ( as far as id is concerned) you should be able to do self join with substraction. CREATE TABLE #t1(id INT  value INT ) INSERT INTO #t1 SELECT 12 UNION SELECT 24 UNION SELECT 33 CREATE TABLE #t2(id INT  value INT ) INSERT INTO #t2 SELECT 12 UNION SELECT 24 UNION SELECT 36 SELECT * FROM #t1 SELECT * FROM #t2 DECLARE @result_t1 BIT =0; DECLARE @result_t2 BIT =0; IF NOT EXISTS( SELECT 1 FROM #t1 INNER JOIN #t1 SJ ON SJ.id = #t1.id-1 WHERE SJ.value>#t1.value) SET @result_t1 = 1; SELECT @result_t1 -- should be 0 - false IF NOT EXISTS( SELECT 1 FROM #t2 INNER JOIN #t2 SJ ON SJ.id = #t2.id-1 WHERE SJ.value>#t2.value) SET @result_t2 = 1; SELECT @result_t2 -- should give 1 - True

Nilesh Shah · Answer

your requirement is not clearly mentioned from what I understand you want true or false returned based on how values are stored in a table? if so then let me tell you the order of storage in a table should never matter you may want to look at option in coding side too like C#