July 19, 2017 at 3:56 am
Hi,
Create Table Test
(
Id int,
name varchar(80)
)
Insert into Test
Select 1, 'A' union All
Select 2, 'B' union All
Select 3, 'C' union All
Select 4, 'D' union All
Select 5, 'E' union All
Select 6, 'F' union All
Select 7, 'G' union All
select * from test
where id = 3.
I am looking to get the 5 rows , where two rows above from id 3 and two rows below from id 3,
Expected Output
Id Name
1 A
2 B
3 C
4 D
5 E.
Could someone have idea how to get around this.
Many Thanks
July 19, 2017 at 4:18 am
Thom A - Wednesday, July 19, 2017 4:01 AMUse BETWEEN:WHERE Id BETWEEN 1 AND 5;
Hi,
I am glad for your reply, to make simplification I had written sample script Id as 1 to 5, but in my real table it had more than two columns and also no serial id like wise i provided early, I was tried to get result using lead and lag function but I didn't succeeded.
July 19, 2017 at 4:25 am
Sangeeth878787 - Wednesday, July 19, 2017 4:18 AMThom A - Wednesday, July 19, 2017 4:01 AMUse BETWEEN:WHERE Id BETWEEN 1 AND 5;Hi,
I am glad for your reply, to make simplification I had written sample script Id as 1 to 5, but in my real table it had more than two columns and also no serial id like wise i provided early, I was tried to get result using lead and lag function but I didn't succeeded.
Could you perhaps provide more realistic DDL and DLM for your set up, and what you'd expect to return? I think I know what you mean, but I'd rather not guess.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 19, 2017 at 6:35 am
July 19, 2017 at 6:54 am
Luis Cazares - Wednesday, July 19, 2017 6:35 AMWill you have gaps in your Id column?
DECLARE
@IDToFind INT = 6,
@NumberOfRows SMALLINT = 5For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 19, 2017 at 6:59 am
Luis Cazares - Wednesday, July 19, 2017 6:35 AMWill you have gaps in your Id column?
Hi,
I am glad for your reply, yes we had gaps in id columns , its is not an primary nor identity columns.
Thank you
July 19, 2017 at 7:13 am
ChrisM@Work - Wednesday, July 19, 2017 6:54 AMLuis Cazares - Wednesday, July 19, 2017 6:35 AMWill you have gaps in your Id column?If yes, then something like this:DECLARE
@IDToFind INT = 6,
@NumberOfRows SMALLINT = 5IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test;
CREATE TABLE #Test (Id int PRIMARY KEY CLUSTERED, [name] varchar(80))INSERT INTO #Test VALUES
(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E'), (6, 'F'), (7, 'G'), (8, 'H'), (9, 'I'), (10, 'J')
;WITH FirstSelection AS (
SELECT TOP((@NumberOfRows/2)+1) *
FROM #test
WHERE Id <= @IDToFind
ORDER BY Id DESC
), SecondSelection AS (
SELECT TOP(@NumberOfRows/2) *
FROM #test
WHERE Id > @IDToFind
ORDER BY Id
)
SELECT *
FROM FirstSelection
UNION ALL
SELECT *
FROM SecondSelection
ORDER BY Id
I am quite happy with the code you have provided which partially works fine for my requirement, In the above #Test table, if we don't have id columns, that mean a table with varchar column only, if we want to see below and above 2 rows when we query like : select name from #Test where name = 'D', [assuming no id column in this table). I am expecting to look B,C,D,E,F.
once again thank you for above code.
July 19, 2017 at 7:27 am
Sangeeth878787 - Wednesday, July 19, 2017 7:13 AMChrisM@Work - Wednesday, July 19, 2017 6:54 AMLuis Cazares - Wednesday, July 19, 2017 6:35 AMWill you have gaps in your Id column?If yes, then something like this:DECLARE
@IDToFind INT = 6,
@NumberOfRows SMALLINT = 5IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test;
CREATE TABLE #Test (Id int PRIMARY KEY CLUSTERED, [name] varchar(80))INSERT INTO #Test VALUES
(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E'), (6, 'F'), (7, 'G'), (8, 'H'), (9, 'I'), (10, 'J')
;WITH FirstSelection AS (
SELECT TOP((@NumberOfRows/2)+1) *
FROM #test
WHERE Id <= @IDToFind
ORDER BY Id DESC
), SecondSelection AS (
SELECT TOP(@NumberOfRows/2) *
FROM #test
WHERE Id > @IDToFind
ORDER BY Id
)
SELECT *
FROM FirstSelection
UNION ALL
SELECT *
FROM SecondSelection
ORDER BY IdI am quite happy with the code you have provided which partially works fine for my requirement, In the above #Test table, if we don't have id columns, that mean a table with varchar column only, if we want to see below and above 2 rows when we query like : select name from #Test where name = 'D', [assuming no id column in this table). I am expecting to look B,C,D,E,F.
once again thank you for above code.
There are numerous alternatives to the code I posted. Can you provide some sample data which is closer to your actual data? This would help folks align their solutions with your requirement.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 19, 2017 at 7:45 am
ChrisM@Work - Wednesday, July 19, 2017 7:27 AMSangeeth878787 - Wednesday, July 19, 2017 7:13 AMChrisM@Work - Wednesday, July 19, 2017 6:54 AMLuis Cazares - Wednesday, July 19, 2017 6:35 AMWill you have gaps in your Id column?If yes, then something like this:DECLARE
@IDToFind INT = 6,
@NumberOfRows SMALLINT = 5IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test;
CREATE TABLE #Test (Id int PRIMARY KEY CLUSTERED, [name] varchar(80))INSERT INTO #Test VALUES
(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E'), (6, 'F'), (7, 'G'), (8, 'H'), (9, 'I'), (10, 'J')
;WITH FirstSelection AS (
SELECT TOP((@NumberOfRows/2)+1) *
FROM #test
WHERE Id <= @IDToFind
ORDER BY Id DESC
), SecondSelection AS (
SELECT TOP(@NumberOfRows/2) *
FROM #test
WHERE Id > @IDToFind
ORDER BY Id
)
SELECT *
FROM FirstSelection
UNION ALL
SELECT *
FROM SecondSelection
ORDER BY IdI am quite happy with the code you have provided which partially works fine for my requirement, In the above #Test table, if we don't have id columns, that mean a table with varchar column only, if we want to see below and above 2 rows when we query like : select name from #Test where name = 'D', [assuming no id column in this table). I am expecting to look B,C,D,E,F.
once again thank you for above code.
There are numerous alternatives to the code I posted. Can you provide some sample data which is closer to your actual data? This would help folks align their solutions with your requirement.
CREATE TABLE #Test
(
UniqueId DEFAULT (newid()),
Name varchar(80),
Location varchar(100)
)
INSERT INTO #Test VALUES (Name,Location)
SELECT 'Server3S','US' UNION ALL
SELECT 'Server5A','UK' UNION ALL
SELECT 'ServerDD','UAE' UNION ALL
SELECT 'ServerGF','CANADA' UNION ALL
SELECT 'ServerLT','MEXICO' UNION ALL
SELECT 'ServerRP','AUSTRALIA' UNION ALL
SELECT 'Serverop','AFRICA'
After I am executing query
select * from #Test
where name = 'ServerGF',
I will get output with one row but I am looking to obtain two(n) rows above and below from that where condition row.
Many Thanks
July 19, 2017 at 7:53 am
Sangeeth878787 - Wednesday, July 19, 2017 7:45 AMChrisM@Work - Wednesday, July 19, 2017 7:27 AMSangeeth878787 - Wednesday, July 19, 2017 7:13 AMChrisM@Work - Wednesday, July 19, 2017 6:54 AMLuis Cazares - Wednesday, July 19, 2017 6:35 AMWill you have gaps in your Id column?If yes, then something like this:DECLARE
@IDToFind INT = 6,
@NumberOfRows SMALLINT = 5IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test;
CREATE TABLE #Test (Id int PRIMARY KEY CLUSTERED, [name] varchar(80))INSERT INTO #Test VALUES
(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E'), (6, 'F'), (7, 'G'), (8, 'H'), (9, 'I'), (10, 'J')
;WITH FirstSelection AS (
SELECT TOP((@NumberOfRows/2)+1) *
FROM #test
WHERE Id <= @IDToFind
ORDER BY Id DESC
), SecondSelection AS (
SELECT TOP(@NumberOfRows/2) *
FROM #test
WHERE Id > @IDToFind
ORDER BY Id
)
SELECT *
FROM FirstSelection
UNION ALL
SELECT *
FROM SecondSelection
ORDER BY IdI am quite happy with the code you have provided which partially works fine for my requirement, In the above #Test table, if we don't have id columns, that mean a table with varchar column only, if we want to see below and above 2 rows when we query like : select name from #Test where name = 'D', [assuming no id column in this table). I am expecting to look B,C,D,E,F.
once again thank you for above code.
There are numerous alternatives to the code I posted. Can you provide some sample data which is closer to your actual data? This would help folks align their solutions with your requirement.
CREATE TABLE #Test
(
UniqueId DEFAULT (newid()),
Name varchar(80),
Location varchar(100)
)INSERT INTO #Test VALUES (Name,Location)
SELECT 'Server3S','US' UNION ALL
SELECT 'Server5A','UK' UNION ALL
SELECT 'ServerDD','UAE' UNION ALL
SELECT 'ServerGF','CANADA' UNION ALL
SELECT 'ServerLT','MEXICO' UNION ALL
SELECT 'ServerRP','AUSTRALIA' UNION ALL
SELECT 'Serverop','AFRICA'After I am executing query
select * from #Test
where name = 'ServerGF',I will get output with one row but I am looking to obtain two(n) rows above and below from that where condition row.
Many Thanks
In order of [name] or [location]?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 19, 2017 at 1:11 pm
This uses a windowed function with a frame of 5 rows (2 preceding and 2 following [plus the current row]) and evaluates to "true" (1) if any record in that frame matches the criteria. If you want to change the criteria, you simply need to update the condition in the CASE statement.
DECLARE @id INT = 3;
Create Table Test
(
Id int,
name varchar(80)
);
Insert into Test
VALUES
(1, 'A')
, (2, 'B')
, (3, 'C')
, (4, 'D')
, (5, 'E')
, (6, 'F')
, (7, 'G')
select * from test
where id = 3;
WITH frame AS (
SELECT *, MAX(CASE WHEN Id = @id THEN 1 ELSE 0 END) OVER(ORDER BY Id ROWS BETWEEN 2 PRECEDING and 2 FOLLOWING) AS is_in_frame
FROM Test
)
SELECT *
FROM frame
WHERE is_in_frame = 1
DROP TABLE Test
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 19, 2017 at 6:11 pm
drew.allen - Wednesday, July 19, 2017 1:11 PMMAX(CASE WHEN Id = @id THEN 1 ELSE 0 END) OVER(ORDER BY Id ROWS BETWEEN 2 PRECEDING and 2 FOLLOWING) AS is_in_frame
Pretty slick. I like it! 🙂
July 26, 2017 at 5:34 pm
drew.allen - Wednesday, July 19, 2017 1:11 PMThis uses a windowed function with a frame of 5 rows (2 preceding and 2 following [plus the current row]) and evaluates to "true" (1) if any record in that frame matches the criteria. If you want to change the criteria, you simply need to update the condition in the CASE statement.
DECLARE @id INT = 3;Create Table Test
(
Id int,
name varchar(80)
);Insert into Test
VALUES
(1, 'A')
, (2, 'B')
, (3, 'C')
, (4, 'D')
, (5, 'E')
, (6, 'F')
, (7, 'G')select * from test
where id = 3;WITH frame AS (
SELECT *, MAX(CASE WHEN Id = @id THEN 1 ELSE 0 END) OVER(ORDER BY Id ROWS BETWEEN 2 PRECEDING and 2 FOLLOWING) AS is_in_frame
FROM Test
)
SELECT *
FROM frame
WHERE is_in_frame = 1DROP TABLE Test
Drew
I am glad for your reply, your query helped me for my requirement. Many Thanks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply