-2

i am having following scenario :

  col1 col2 col3 
   10   2    13
   13   4    14
   14   5    15
   15   6    16

form the above given table col3 value(13) in row1 is seen in col1 value(13) in row2 and this chain continues...

here my input column is col1 and i have to get col3 value where the chain ends up
condition is col3 value should match with next row col1 value.

example input-----> col1 = 10, output----->col3 = 16.

another table :

col1 col2 col3 
151   6    162
162   4    163
163   67   164
102   2    101
101   4    107
107   5    103
v1    2    v2
v2    4    v3
v3    5    v5 

here is my input -->(151,v1,102) , output---->(164,v5,103)

unfortunately, i am not supposed to use stored procedures.

i am struggling on this for couple of hours...please help me.

  • can you give another table, with the expected output. – g2server Jun 24 '14 at 9:18
  • @g2server i have updated my question with another table – vinay Jun 24 '14 at 9:27
  • Who is telling you you are "not supposed to use stored procedures"? Also, a query is a query, whether it is contained inside a stored procedure or not. I don't know why people state these artificial and meaningless constraints... – Aaron Bertrand Jun 24 '14 at 13:26
-1

you can use the LAG or LEAD functions (MSDN)

or you can use recurrsion with CTE,

CTE example:

    IF OBJECT_ID('tempdb..#TBL') IS NOT NULL DROP TABLE #TBL
    CREATE TABLE #TBL
    (
        COL1 INT NOT NULL,
        COL2 INT NOT NULL,
        COL3 INT NOT NULL
    )
    INSERT #TBL
            ( COL1, COL2, COL3 )
    VALUES  ( 10,2,13),(13,4,14),(14,5,15),(15,6,16)

    DECLARE @INPUT INT = 10
    --This is the only part you need
    ;WITH ReccursiveCTE AS (
    SELECT TOP 1 COL1,COL2,COL3,1 LVL FROM #TBL WHERE COL1 = @INPUT
    UNION ALL
    SELECT  T.COL1 ,
            T.COL2 ,
            T.COL3,
            C.LVL + 1 FROM ReccursiveCTE C
    INNER JOIN #TBL T ON C.COL3 = T.COL1
    )
    SELECT TOP 1 COL3 AS Result FROM ReccursiveCTE ORDER BY LVL desc
--end

:)

shimon.

  • i cannot use stored procedures – vinay Jun 24 '14 at 7:54
  • thats not a stored procedure... and you dont need the create table / drop table / insert parts of the code because you allready have the data and table – Shimon Gb Gibraltar Jun 24 '14 at 7:56
  • oh can i use it directly as query ..let me try this – vinay Jun 24 '14 at 7:57
  • i have tried it in ---sqlfiddle.com/#!6/f4f24/2 ..its throws an error – vinay Jun 24 '14 at 8:00
  • hey if i give input value from middle of records its giving me wrong value can u plz check here sqlfiddle.com/#!6/afea6/5 – vinay Jun 24 '14 at 8:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.