How to get top and bottom rows from where condition

Question

Post reply

How to get top and bottom rows from where condition

Sangeeth Raj

Hall of Fame

Points: 3541
More actions
July 19, 2017 at 3:56 am

#324596

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

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

Use BETWEEN:
WHERE Id BETWEEN 1 AND 5;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Sangeeth Raj Hall of Fame Points: 3541 More actions · Answer 2

Thom A - Wednesday, July 19, 2017 4:01 AM
Use 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.

Thom A SSC Guru Points: 99130 More actions · Answer 3

Sangeeth878787 - Wednesday, July 19, 2017 4:18 AM
Thom A - Wednesday, July 19, 2017 4:01 AM
Use 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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Will you have gaps in your Id column?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Luis Cazares - Wednesday, July 19, 2017 6:35 AM
Will you have gaps in your Id column?

If yes, then something like this:

DECLARE 
 @IDToFind INT = 6,
 @NumberOfRows SMALLINT = 5

IF 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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Sangeeth Raj Hall of Fame Points: 3541 More actions · Answer 6

Luis Cazares - Wednesday, July 19, 2017 6:35 AM
Will 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

Sangeeth Raj Hall of Fame Points: 3541 More actions · Answer 7

ChrisM@Work - Wednesday, July 19, 2017 6:54 AM
Luis Cazares - Wednesday, July 19, 2017 6:35 AM
Will you have gaps in your Id column?
If yes, then something like this:
DECLARE @IDToFind INT = 6, @NumberOfRows SMALLINT = 5
IF 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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Sangeeth878787 - Wednesday, July 19, 2017 7:13 AM
ChrisM@Work - Wednesday, July 19, 2017 6:54 AM
Luis Cazares - Wednesday, July 19, 2017 6:35 AM
Will you have gaps in your Id column?
If yes, then something like this:
DECLARE @IDToFind INT = 6, @NumberOfRows SMALLINT = 5
IF 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.

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Sangeeth Raj Hall of Fame Points: 3541 More actions · Answer 9

ChrisM@Work - Wednesday, July 19, 2017 7:27 AM
Sangeeth878787 - Wednesday, July 19, 2017 7:13 AM
ChrisM@Work - Wednesday, July 19, 2017 6:54 AM
Luis Cazares - Wednesday, July 19, 2017 6:35 AM
Will you have gaps in your Id column?
If yes, then something like this:
DECLARE @IDToFind INT = 6, @NumberOfRows SMALLINT = 5
IF 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.
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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Sangeeth878787 - Wednesday, July 19, 2017 7:45 AM
ChrisM@Work - Wednesday, July 19, 2017 7:27 AM
Sangeeth878787 - Wednesday, July 19, 2017 7:13 AM
ChrisM@Work - Wednesday, July 19, 2017 6:54 AM
Luis Cazares - Wednesday, July 19, 2017 6:35 AM
Will you have gaps in your Id column?
If yes, then something like this:
DECLARE @IDToFind INT = 6, @NumberOfRows SMALLINT = 5
IF 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.
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]?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

drew.allen SSC Guru Points: 76988 More actions · Answer 11

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

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 12

drew.allen - Wednesday, July 19, 2017 1:11 PM
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

Pretty slick. I like it! 🙂

Sangeeth Raj Hall of Fame Points: 3541 More actions · Answer 13

drew.allen - Wednesday, July 19, 2017 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

I am glad for your reply, your query helped me for my requirement. Many Thanks