How to get middle rows data from a set of rows in a table

Question

How to get middle rows data from a set of rows in a table

Viewing 4 posts - 16 through 18 (of 18 total)

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

psmahesh115 SSC Enthusiast Points: 196 More actions · Answer 1

sasil - Monday, March 15, 2010 5:36 AM
Hi Friends,I have a table of 1000 records of data. If i want to get data of rows from 501 to 700 from the table without using any condition what I have to do.Please suggest a solution.

select * from tablename where rownum<=700
minus
elect * from tablename where rownum<500

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

psmahesh115 - Wednesday, February 7, 2018 4:56 AM
sasil - Monday, March 15, 2010 5:36 AM
Hi Friends,I have a table of 1000 records of data. If i want to get data of rows from 501 to 700 from the table without using any condition what I have to do.Please suggest a solution.
select * from tablename where rownum<=700
minus
elect * from tablename where rownum<500

I always appreciate it when someone tries to improve an answer for an 8 year old post but your code won't help unless there's a fresh copy of a column called RowNum on the table. Also, "MINUS" doesn't work in SQL Server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 3

Jeff Moden - Wednesday, February 7, 2018 7:04 AM
psmahesh115 - Wednesday, February 7, 2018 4:56 AM
sasil - Monday, March 15, 2010 5:36 AM
Hi Friends,I have a table of 1000 records of data. If i want to get data of rows from 501 to 700 from the table without using any condition what I have to do.Please suggest a solution.
select * from tablename where rownum<=700
minus
elect * from tablename where rownum<500
I always appreciate it when someone tries to improve an answer for an 8 year old post but your code won't help unless there's a fresh copy of a column called RowNum on the table. Also, "MINUS" doesn't work in SQL Server.

and what if the second dataset were elected with a hanging chad? (since we're already going back in time)

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

drew.allen SSC Guru Points: 76985 More actions · Answer 4

Jeff Moden - Wednesday, February 7, 2018 7:04 AM
psmahesh115 - Wednesday, February 7, 2018 4:56 AM
sasil - Monday, March 15, 2010 5:36 AM
Hi Friends,I have a table of 1000 records of data. If i want to get data of rows from 501 to 700 from the table without using any condition what I have to do.Please suggest a solution.
select * from tablename where rownum<=700
minus
elect * from tablename where rownum<500
I always appreciate it when someone tries to improve an answer for an 8 year old post but your code won't help unless there's a fresh copy of a column called RowNum on the table. Also, "MINUS" doesn't work in SQL Server.

You could use EXCEPT instead of MINUS, but it would still include an extra row as written.

In SQL 2012, they introduced OFFSET/FETCH specifically to handle paging.
SELECT * FROM tablename ORDER BY somefield OFFSET 500 ROWS FETCH NEXT 200 ROWS ONLY

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA