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

Question

Post reply

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

sasil

Old Hand

Points: 399
More actions
March 15, 2010 at 5:36 am

#156783

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.

Viewing 15 posts - 1 through 15 (of 18 total)

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

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

It is not magic, you must use some criteria in the WHERE clause.

Will your keyboard not type the word "WHERE", or what?

Andrew SQLDBA

Cyberspy SSC Eights! Points: 920 More actions · Answer 2

Hi,

I presume this is for paged results to a web page or something similar.

You can use the ROW_NUMBER() OVER statement to add a new column with row numbers, so you can just select the rows you want

; WITH MySelectedRows AS (

SELECT ROW_NUMBER() OVER (ORDER BY MySortColumn DESC) as RowNumber, *

FROM MyTable

WHERE <some clause>

)

SELECT * FROM MySelectedRows

WHERE RowNumber BETWEEN 501 AND 700

Adam

Note the ; before the WITH statement - the statement before a WITH statement must be followed by a ; , so adding it here ensures that is the case

Cyberspy SSC Eights! Points: 920 More actions · Answer 3

AndrewSQLDBA (3/15/2010)
It is not magic, you must use some criteria in the WHERE clause.
Will your keyboard not type the word "WHERE", or what?
Andrew SQLDBA

Was that really necessary?

It is quite a reasonable question that I'm sure has foxed plenty of people before.

If you can't give a helpful answer, why not just move onto another question?

Adam

sasil Old Hand Points: 399 More actions · Answer 4

sasil

Old Hand

Points: 399

March 15, 2010 at 6:30 am

#1133641

Thanks for your solution Adam.

Paul White SSC Guru Points: 150467 More actions · Answer 5

Cyberspy (3/15/2010)
AndrewSQLDBA (3/15/2010)
It is not magic, you must use some criteria in the WHERE clause.
Will your keyboard not type the word "WHERE", or what?
Andrew SQLDBA
Was that really necessary?
It is quite a reasonable question that I'm sure has foxed plenty of people before.
If you can't give a helpful answer, why not just move onto another question?
Adam

I read it as humourous :blink::unsure:

Benefit of the doubt eh? :Whistling:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

steveb. SSC-Forever Points: 46733 More actions · Answer 6

you can also do this without using WHERE by using EXCEPT, though without knowing the order of the data the results may vary

SELECT TOP 700 * FROM yourTable

EXCEPT

SELECT TOP 500 * FROM yourTable

Paul White SSC Guru Points: 150467 More actions · Answer 7

steveb. (3/15/2010)
you can also do this without using WHERE by using EXCEPT, though without knowing the order of the data the results may vary

Very creative! Horrible performance though, and quite likely to return erroneous results (unless run in a transaction running at the SERIALIZABLE isolation level, or under one of the row-versioning levels...I guess). I do admire the thought process behind the idea though. Nice.

As an aside, for paging problems, I often write the query seek down a key to find the one page of keys for display, and then 'look up' the row data from those keys. It is also frequently useful to optimize for first-half and last-half results by seeking forward or backward on the index.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

steveb. SSC-Forever Points: 46733 More actions · Answer 8

Thanks Paul, yeah I wouldn't use it in 'real-life' i would tend to go for the ROW_NUMBER example posted

Paul White SSC Guru Points: 150467 More actions · Answer 9

I had a feeling you would 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Cyberspy SSC Eights! Points: 920 More actions · Answer 10

As an aside, for paging problems, I often write the query seek down a key to find the one page of keys for display, and then 'look up' the row data from those keys. It is also frequently useful to optimize for first-half and last-half results by seeking forward or backward on the index.

Paul,

This sounds interesting. Any chance of an example?

Thanks

Adam

Paul White SSC Guru Points: 150467 More actions · Answer 11

Cyberspy (3/15/2010)
This sounds interesting. Any chance of an example?

Adam, this is something I get asked quite a lot, so I think I will write an article about it.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Cyberspy SSC Eights! Points: 920 More actions · Answer 12

Paul White (3/15/2010)
Cyberspy (3/15/2010)
This sounds interesting. Any chance of an example?
Adam, this is something I get asked quite a lot, so I think I will write an article about it.

Paul,

That sounds great!

Could I ask you a favour - update this thread when you've published it, so I can read it?

Thanks

Adam

Paul White SSC Guru Points: 150467 More actions · Answer 13

Sure thing. If I remember, I will send you a PM too.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Cyberspy SSC Eights! Points: 920 More actions · Answer 14

Cyberspy

SSC Eights!

Points: 920

March 16, 2010 at 4:15 pm

#1134897

Thanks!

Adam