|
View previous topic :: View next topic
|
| Author |
Message |
Mukesh.py
Currently Banned
New User
Joined: 22 Sep 2008
Posts: 17
Location: Chennai, India.
|
|
|
|
Hi, i have a tabel having 1000 rows . i want to select the rows between 100 and 200. how can i achiev this?
i/p
r1
r2
.
.
.
r99
r100
.
.
.
r200
r201
.
.
.
.
.
.
.
r1000
o/p
r100
r101
.
.
.
.
.
.
r199
r200
Thanks, |
|
| Back to top |
|
 |
Ajay Baghel
Active User
.jpg)
Joined: 25 Apr 2007
Posts: 206
Location: Bangalore
|
|
|
|
| use scrollble cursor. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| i want to select the rows between 100 and 200. |
There are no rows between 100 and 200. You could retrieve 100 rows, but there is nothing that makes them from 100 to 200. An existing sequential file might have rows (records) 100-200 but a database table does not.
If your data actually contains rnn, you could "select" where the value was > r099 and < r201.
What are you trying to accomplish? |
|
| Back to top |
|
|
SRINIVASAN OJ
New User
Joined: 10 Oct 2008
Posts: 1
Location: usa
|
|
|
|
You can use Multirow Fetch.
Declare a corsor for rowset positioning like below,
EXEC SQL
DECLARE C1 CURSOR
WITH ROWSET POSITIONING
FOR SELECT * FROM TABLE NAME;
THEN IN FETCH
FETCH ROWSET
STARTING AT ABSOLUTE 100 FROM C1
FOR 100 ROWS INTO ...
The ABSOLUTE 100 Is the start position. But every time after executing the query the cursor will be again positioned to the 100th row (as in this case) |
|
| Back to top |
|
|
nabarundas
New User
Joined: 21 Jun 2007
Posts: 28
Location: pune
|
|
|
|
Try this query:
| Code: |
(select * from Table_name
Fetch first 200 rows only)
except
(select * from Table_name
Fetch first 100 rows only) |
|
|
| Back to top |
|
|
anil.csk
New User
Joined: 22 Oct 2007
Posts: 16
Location: Noida
|
|
|
|
hi,
you can use the cursor for fecth the 100 rows.
but at the time of declaring the cursor in where condition use the pointer at 100 th row so that cursor should start to read the data from 100th row and and go upto 200 records as you have given the you want to access the data for 100.
by doing this way you do as I thinks so..
suggestion are welcome.. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
To repeat:
| Quote: |
| There are no rows between 100 and 200. You could retrieve 100 rows, but there is nothing that makes them from 100 to 200. |
Just because 100 rows are returned, they are not row 100 to 200 of that table. . .
This is one of the most basic principles of database work and should be understood. It doesn't matter it is your boss, your teacher, or your client saying it - there is no 100th row in a table. . . In your work, you will need to meet the requirement, but you should also understand the concept. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| nabarundas wrote: |
Try this query:
| Code: |
(select * from Table_name
Fetch first 200 rows only)
except
(select * from Table_name
Fetch first 100 rows only) |
|
Without ORDER BY clause, this is just a garbage collection of rows.. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| Without ORDER BY clause, this is just a garbage collection of rows.. |
Most certainly.
And with an ORDER BY, the data is only for that particular set - not the table. . . |
|
| Back to top |
|
|
nabarundas
New User
Joined: 21 Jun 2007
Posts: 28
Location: pune
|
|
|
|
Hi Anuj D.
suppose my table name is TABLEA having the following data
| Code: |
EMPID NAME
1 aaa
2 bbb
3 ccc
4 ddd
5 eee |
where EMPID is the primary key.
Now i have executed the query
| Code: |
(select * from nabarun.tablea
fetch first 5 rows only)
except
(select * from nabarun.tablea
fetch first 2 rows only) |
it is giving me the following result
| Code: |
EMPID NAME
----- ----
3 ccc
4 ddd
5 eee |
3 record(s) selected.
I am not getting any garbage.
So could you plz highlight the point that how I can get garbage collection of row?
Regards,
Nabarun
Edited: Added BBcode....Anuj |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| So could you plz highlight the point that how I can get garbage collection of row? |
You do not get physical garbage, You get logical garbage
from Your sample what would You consider as first rows ??
You have to define the rules first !
the only case where a count is logicaly correct is when You are grading
quantities....
order by salary descending fech first 5 only
and You get the top 5 salaries  |
|
| Back to top |
|
|
nabarundas
New User
Joined: 21 Jun 2007
Posts: 28
Location: pune
|
|
|
|
Hi enrico,
I got your point.
But when I look at Access plan graph I can see GROUP BY is done internally. The Access path of my table look like this:
| Code: |
RETURN(1) 15.15
|
|
FILTER(3) 15.15
|
|
GRPBY(5) 15.15
|
|
TBSCAN(7) 15.15
|
|
SORT(9) 15.15
|
|
UNION(11) 15.15
/ \
/ \
/ \
TBSCAN(13) 7.57 TBSCAN(13) 7.57
| |
| |
NABARUN.TABLEA NABARUN.TABLEA
|
Probably thats why I am not getting any garbage. Am I right? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| Probably thats why I am not getting any garbage. Am I right? |
No, you have rather missed the whole point.
When Anuj used the term "garbage collection" it did not mean the same as the term means to you. I suspect you are referring to the process of removing superflous data periodically.
The reference Anuj made was that the set was not predictable and thus was garbage. |
|
| Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark
|
|
|
|
we should not refer to data in the table by row no...like 100th row or 200th row.... Its meaningless. We never know what is 100th row.!!!
This type of logic will result in fatal error. |
|
| Back to top |
|
|
|
|
