none
One record under

    Question

  • One record overHi
    I have a table that has 2 columns - ID, Name
    I did a query and the result is records that their ID: 3,8,11,14, etc.
    What is the simplest way to get the records that are in one line above - - 4,9,12,15 etc.Hi
    I have a table that has 2 columns - ID, Name
    I did a query and the result is records that their ID: 3,8,11,14, etc.
    What is the simplest way to get the records that are in one line above - - 4,9,12,15 etc.sdad

    Hi
    I have a table that has 2 columns - ID, Name
    I did a query and the result is records that their ID: 3,8,11,14, etc.
    What is the simplest way to get the records that are in one line above - - 4,9,12,15 etc.

    Hi
    I have a table that has 2 columns - ID, Name
    I did a query and the result is records that their ID: 3,8,11,14, etc.
    What is the simplest way to get the records that are in one line above - - 4,9,12,15 etc.

    Hi
    I have a table that has 2 columns - ID, Name
    I did a query and the result is records that their ID: 3,8,11,14, etc.
    What is the simplest way to get the records that are in one line above - - 4,9,12,15 etc.
    Sunday, March 17, 2013 7:53 AM

Answers

  • how are you selecting 3,8,11,14?? you can use below as reference.....you can use row_number() if the ID numbers are not sequential
    declare @t table (sno int,sno1 int)
    
    insert into @t
    values (2,9),(7,9),(10,9),(13,9),(3,9),(8,9),(11,9),(14,9)
    ,(17,9),(18,9),(19,9)
    
    select * from @t where sno in (select sno-1 from @t where sno in (3,8,11,14))


    Hope it Helps!!

    Sunday, March 17, 2013 8:11 AM
  • Follow the AdventureWorks2012 example:

    WITH CTE AS (SELECT RN=ROW_NUMBER() OVER (ORDER BY ProductID), ProductID, ProductNumber, Name, ListPrice FROM Production.Product) SELECT * FROM CTE WHERE ProductID % 97 = 0 ORDER BY RN; GO /* RN ProductID ProductNumber Name ListPrice 67 388 HN-5162 Hex Nut 11 0.00 164 485 MS-2259 Metal Sheet 4 0.00 209 679 RC-0291 Rear Derailleur Cage 0.00 281 776 BK-M82B-42 Mountain-100 Black, 42 3374.99 378 873 PK-7098 Patch Kit/8 Patches 2.29 475 970 BK-T44U-46 Touring-2000 Blue, 46 1214.85 */ -- Get the following in the sequence

    -- Notice the JOIN ON predicate

    WITH CTE AS (SELECT RN=ROW_NUMBER() OVER (ORDER BY ProductID), ProductID, ProductNumber, Name, ListPrice FROM Production.Product), CTE1 AS (SELECT * FROM CTE WHERE ProductID % 97 = 0 ) SELECT * FROM CTE INNER JOIN CTE1 ON CTE1.RN +1 = CTE.RN ORDER BY CTE.RN; GO /* RN ProductID ProductNumber Name ListPrice RN ProductID ProductNumber Name ListPrice 68 389 HN-5400 Hex Nut 2 0.00 67 388 HN-5162 Hex Nut 11 0.00 165 486 MS-2341 Metal Sheet 5 0.00 164 485 MS-2259 Metal Sheet 4 0.00 210 680 FR-R92B-58 HL Road Frame - Black, 58 1489.3326 209 679 RC-0291 Rear Derailleur Cage 0.00 282 777 BK-M82B-44 Mountain-100 Black, 44 3374.99 281 776 BK-M82B-42 Mountain-100 Black, 42 3374.99 379 874 SO-R809-M Racing Socks, M 8.99 378 873 PK-7098 Patch Kit/8 Patches 2.29 476 971 BK-T44U-50 Touring-2000 Blue, 50 1214.85 475 970 BK-T44U-46 Touring-2000 Blue, 46 1214.85 */



    Kalman Toth Database & OLAP Architect sqlusa.com
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Sunday, March 17, 2013 9:43 AM
    Moderator

All replies

  • Hi
    I have a table that has 2 columns - ID, Name
    I did a query and the result is records that their ID: 3,8,11,14, etc.
    What is the simplest way to get the records that are in one line above - - 4,9,12,15 etc.Hi
    I have a table that has 2 columns - ID, Name
    I did a query and the result is records that their ID: 3,8,11,14, etc.
    What is the simplest way to get the records that are in one line above - - 4,9,12,15 etc.sdad

    Hi
    I have a table that has 2 columns - ID, Name
    I did a query and the result is records that their ID: 3,8,11,14, etc.
    What is the simplest way to get the records that are in one line above - - 4,9,12,15 etc.

    Hi
    I have a table that has 2 columns - ID, Name
    I did a query and the result is records that their ID: 3,8,11,14, etc.
    What is the simplest way to get the records that are in one line above - - 4,9,12,15 etc.

    Hi
    I have a table that has 2 columns - ID, Name
    I did a query and the result is records that their ID: 3,8,11,14, etc.
    What is the simplest way to get the records that are in one line above - - 4,9,12,15 etc.
    Sunday, March 17, 2013 7:53 AM
  • Hi
    I have a table that has 2 columns - ID, Name
    I did a query and the result is records that their ID: 3,8,11,14, etc.
    What is the simplest way to get the records that are in one line under - 2,7,10,13 etc.
    Sunday, March 17, 2013 7:55 AM
  • how are you selecting 3,8,11,14?? you can use below as reference.....you can use row_number() if the ID numbers are not sequential
    declare @t table (sno int,sno1 int)
    
    insert into @t
    values (2,9),(7,9),(10,9),(13,9),(3,9),(8,9),(11,9),(14,9)
    ,(17,9),(18,9),(19,9)
    
    select * from @t where sno in (select sno-1 from @t where sno in (3,8,11,14))


    Hope it Helps!!

    Sunday, March 17, 2013 8:11 AM
  • declare @t table (sno int,sno1 int)
    
    insert into @t
    values (4,9),(9,9),(12,9),(15,9),(3,9),(8,9),(11,9),(14,9)
    ,(17,9),(18,9),(19,9)
    
    select * from @t where sno in (select sno+1 from @t where sno in (3,8,11,14))
    by line above you mean ID number above??

    Hope it Helps!!


    • Edited by Stan210 Sunday, March 17, 2013 8:21 AM
    • Proposed as answer by Sarat Babu (SS) Monday, March 18, 2013 6:33 AM
    Sunday, March 17, 2013 8:21 AM
  • Hi

    its a query that the resaults are records : 3,8,11,14 etc ...

    i need a query that will give me the records : 2,7,10,13 etc...

    those numbers are the id

    Sunday, March 17, 2013 8:53 AM
  • does the syntax not help?? I do not know your query on how you got 3,8,11,14..so, I the only thing I can do is

    declare @t table (sno int,sno1 int)
    
    insert into @t
    values (2,9),(7,9),(10,9),(13,9),(3,9),(8,9),(11,9),(14,9)
    ,(17,9),(18,9),(19,9)
    
    select sno-1 from @t where sno in (3,8,11,14)


    Hope it Helps!!

    Sunday, March 17, 2013 8:57 AM
  • Follow the AdventureWorks2012 example:

    WITH CTE AS (SELECT RN=ROW_NUMBER() OVER (ORDER BY ProductID), ProductID, ProductNumber, Name, ListPrice FROM Production.Product) SELECT * FROM CTE WHERE ProductID % 97 = 0 ORDER BY RN; GO /* RN ProductID ProductNumber Name ListPrice 67 388 HN-5162 Hex Nut 11 0.00 164 485 MS-2259 Metal Sheet 4 0.00 209 679 RC-0291 Rear Derailleur Cage 0.00 281 776 BK-M82B-42 Mountain-100 Black, 42 3374.99 378 873 PK-7098 Patch Kit/8 Patches 2.29 475 970 BK-T44U-46 Touring-2000 Blue, 46 1214.85 */ -- Get the following in the sequence

    -- Notice the JOIN ON predicate

    WITH CTE AS (SELECT RN=ROW_NUMBER() OVER (ORDER BY ProductID), ProductID, ProductNumber, Name, ListPrice FROM Production.Product), CTE1 AS (SELECT * FROM CTE WHERE ProductID % 97 = 0 ) SELECT * FROM CTE INNER JOIN CTE1 ON CTE1.RN +1 = CTE.RN ORDER BY CTE.RN; GO /* RN ProductID ProductNumber Name ListPrice RN ProductID ProductNumber Name ListPrice 68 389 HN-5400 Hex Nut 2 0.00 67 388 HN-5162 Hex Nut 11 0.00 165 486 MS-2341 Metal Sheet 5 0.00 164 485 MS-2259 Metal Sheet 4 0.00 210 680 FR-R92B-58 HL Road Frame - Black, 58 1489.3326 209 679 RC-0291 Rear Derailleur Cage 0.00 282 777 BK-M82B-44 Mountain-100 Black, 44 3374.99 281 776 BK-M82B-42 Mountain-100 Black, 42 3374.99 379 874 SO-R809-M Racing Socks, M 8.99 378 873 PK-7098 Patch Kit/8 Patches 2.29 476 971 BK-T44U-50 Touring-2000 Blue, 50 1214.85 475 970 BK-T44U-46 Touring-2000 Blue, 46 1214.85 */



    Kalman Toth Database & OLAP Architect sqlusa.com
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Sunday, March 17, 2013 9:43 AM
    Moderator
  • Please read any book on RDBMS and pay attention to the parts about First Normal Form (1NF). This will keep you from kludge hunting on SQL forums, but if you do not want to be a good SQL programmer then the  current popular kludge is XML. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, March 17, 2013 2:25 PM
  • Try like this
    declare @table  table (id int)
    insert into @table
    select 1 union
    select 2 union
    select 3 union
    select 4 union
    select 5 union
    select 6 union
    select 7 union
    select 8 union
    select 9 union
    select 10 union
    select 11 union
    select 12 union
    select 13 union
    select 14 union
    select 15 
    
    
    declare @table2 table (id int)
    insert into @table2
    select 3 union
    select 8 union
    select 11 union
    select 14 
    
    select a.id from @table a 
    join @table2 b on a.id = b.id+1
    


    Regards, Dineshkumar
    Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you

    Monday, March 18, 2013 6:07 AM
  • declare @table  table (id int)
    insert into @table
    select 1 union
    select 2 union
    select 3 union
    select 4 union
    select 5 union
    select 6 union
    select 7 union
    select 8 union
    select 9 union
    select 10 union
    select 11 union
    select 12 union
    select 13 union
    select 14 union
    select 15 
    
    
    declare @table2 table (id int)
    insert into @table2
    select 3 union
    select 8 union
    select 11 union
    select 14 
    
    select a.id from @table a 
    join @table2 b on a.id = b.id-1
    


    Regards, Dineshkumar
    Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you

    Monday, March 18, 2013 6:09 AM
  • declare @table  table (id int)
    insert into @table
    select 1 union
    select 2 union
    select 3 union
    select 4 union
    select 5 union
    select 6 union
    select 7 union
    select 8 union
    select 9 union
    select 10 union
    select 11 union
    select 12 union
    select 13 union
    select 14 union
    select 15 
    
    
    declare @table2 table (id int)
    insert into @table2
    select 3 union
    select 8 union
    select 11 union
    select 14 
    
    select a.id from @table a 
    join @table2 b on a.id = b.id+1
    


    Regards, Dineshkumar
    Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you

    Monday, March 18, 2013 6:09 AM
  • Just another way directly from the original output,
    declare @t table (sno int,sno1 int)
    
    insert into @t
    values (4,9),(9,9),(12,9),(15,9),(3,9),(8,9),(11,9),(14,9)
    ,(17,9),(18,9),(19,9)
    
    
    select A.* from @t A 
    inner join (select sno from @t where sno in (3,8,11,14)) B
    On A.sno= B.sno+1
    


    Thanks Sarat --Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

    Monday, March 18, 2013 6:35 AM
  • Hi,

    Try this..

    DECLARE @test TABLE (sno INT)

    INSERT INTO @test
    VALUES (2),
      (3),
      (7),
      (8),
      (10),
      (11),
      (13),
      (14)


    SELECT b.*
    FROM @test a
    JOIN @test b ON b.sno = a.sno-1
    WHERE a.sno IN (3,8,11,14)

    Monday, March 18, 2013 5:23 PM