Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
One record under

Answered One record under

  • Sunday, March 17, 2013 7:53 AM
     
     
    Sign In to Vote
    0
    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.
     

All Replies

  • Sunday, March 17, 2013 7:53 AM
     
     
    Sign In to Vote
    0
    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:55 AM
     
     
    Sign In to Vote
    0
    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 8:11 AM
     
     Answered Has Code
    Sign In to Vote
    0
    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:21 AM
     
      Has Code
    Sign In to Vote
    0
     
    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:53 AM
     
     
    Sign In to Vote
    0

    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:57 AM
     
      Has Code
    Sign In to Vote
    0

    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 9:43 AM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    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 2:25 PM
     
     
    Sign In to Vote
    0
    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

     
  • Monday, March 18, 2013 6:07 AM
     
      Has Code
    Sign In to Vote
    0
    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:09 AM
     
      Has Code
    Sign In to Vote
    0
     
    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
     
      Has Code
    Sign In to Vote
    0
     
    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:35 AM
     
      Has Code
    Sign In to Vote
    0
    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 5:23 PM
     
     
    Sign In to Vote
    0

    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)