# 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

• 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

```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 predicateWITH 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

### 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 Sunday, March 17, 2013 8:21 AM
• Proposed as answer by 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

```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 predicateWITH 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
• 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

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

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

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