Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to Select row100 to row200 from a tabel having 1000 rows

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Mukesh.py
Currently Banned

New User


Joined: 22 Sep 2008
Posts: 17
Location: Chennai, India.

PostPosted: Mon Oct 13, 2008 6:55 pm    Post subject: How to Select row100 to row200 from a tabel having 1000 rows
Reply with quote

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
View user's profile Send private message

Ajay Baghel

Active User


Joined: 25 Apr 2007
Posts: 205
Location: Bangalore

PostPosted: Mon Oct 13, 2008 7:26 pm    Post subject: Reply to: How to Select row100 to row200 from a tabel having
Reply with quote

use scrollble cursor.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Oct 14, 2008 12:36 am    Post subject:
Reply with quote

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
View user's profile Send private message
SRINIVASAN OJ

New User


Joined: 10 Oct 2008
Posts: 1
Location: usa

PostPosted: Tue Oct 14, 2008 11:40 am    Post subject: Reply to: How to Select row100 to row200 from a tabel having
Reply with quote

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
View user's profile Send private message
nabarundas

New User


Joined: 21 Jun 2007
Posts: 28
Location: pune

PostPosted: Wed Oct 15, 2008 5:03 pm    Post subject:
Reply with quote

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
View user's profile Send private message
anil.csk

New User


Joined: 22 Oct 2007
Posts: 16
Location: Noida

PostPosted: Wed Oct 15, 2008 6:51 pm    Post subject: Reply to: How to Select row100 to row200 from a tabel having
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Oct 15, 2008 11:35 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed Oct 15, 2008 11:52 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Oct 16, 2008 1:15 am    Post subject:
Reply with quote

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
View user's profile Send private message
nabarundas

New User


Joined: 21 Jun 2007
Posts: 28
Location: pune

PostPosted: Thu Oct 16, 2008 10:25 am    Post subject: Hi Anuj
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Thu Oct 16, 2008 10:59 am    Post subject: Reply to: How to Select row100 to row200 from a tabel having
Reply with quote

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 icon_biggrin.gif
Back to top
View user's profile Send private message
nabarundas

New User


Joined: 21 Jun 2007
Posts: 28
Location: pune

PostPosted: Thu Oct 16, 2008 11:24 am    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Oct 16, 2008 1:14 pm    Post subject:
Reply with quote

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
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Thu Oct 16, 2008 2:18 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Can we use OPTIMIZE FOR 1 ROWS for fe... cvijay784 DB2 1 Fri Aug 05, 2016 11:56 am
No new posts Select first 2 group data vice_versa DFSORT/ICETOOL 10 Mon Jun 13, 2016 2:21 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us