How can i retrieve record ONLY From 300 - 400 in amillion Record Table?

[keonglah]

HI all
Thanks for your time.. I have a question here..
How can i retrieve record ONLY From XXX - XXX in a million Record Table?
eg.
I have a table , UserMaster hold 1000 Record.
I just want to retrieved ONLY record from 300 - 400 active record?
The table have a ID and auto added by one.
Some record maybe deleted. eg.. the ID may hold 1..
4,5,7,9,10...15,17,19,20,21,22....

Database used : MSSQL / Oracle


Thanks for help.

Regard
keonglah

[toby007]

Hi there

You can try this - it seems to work

SELECT *
FROM tableX
WHERE rowid in (
SELECT rowid FROM tableX
WHERE rownum <= 400
MINUS
SELECT rowid FROM tableX
WHERE rownum < 300);

Thius will retrieve all records with a row number between 300 & 400

Hope this helps

[CF_Oracle]

Why not nto use simple
SELECT *
FROM tableX
WHERE rownum between 300 and 400?

[jonwrob]

SELECT *
FROM tableX
WHERE rownum between 300 and 400?

Did you try it? Because it returns no rows. Oracle begins the calculation of rownum at 1 for any recordset it returns.

JR

[CF_Oracle]

JR, you are right. I used not autonumber but regular number coulmn format.

[krisbrixon]

If this is for paging.

Try this:
SELECT TOP 100 * FROM tablex
for the first set then record the last record ID. Then for the second,
third... pages
SELECT TOP 100 * FROM tablex WHERE ID > #URL.lastID#

[keonglah]

HI.. Thanks for all reply..

Maybe i try to rewrite my question..~

Im have a table , for example, UserMaster hold million Records. I would like
to generate a page where is only show 100 Records and there is a next and
previous button allow user move to the next or pevious page. IF just used a
[select fiend Name from UserMaster] it will store million of record in a memory
but i just want to used only 100 Record per page. Inorder to avoid this, how
can i retrieved ONLY 100 active record per page by using query, store pro or
view?

Senario
The table have field call UserID and auto added by one.
Some record maybe deleted.
eg.. The ID in the table will be like.. 300,301,302,305,306..400 (303 and 304
record is deleted.) Inorder to retrieved 100 record per page.. the ID is start
from 300 till 402.


To krisbrixon :

>If this is for paging.

>Try this:
>SELECT TOP 100 * FROM tablex
>for the first set then record the last record ID. Then for the second,

third... pages

>SELECT TOP 100 * FROM tablex WHERE ID > #URL.lastID#

This is a pretty good idea.. but i guess we have to change url.lastID to
session.lastID to avoid user manually change the ID.

How bout using the following query?
SELECT TOP 100 * FROM tablex
WHERE ID beteween [StartID] add [EndID]

Question.. How can i get the StartID and EndID value??