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 retrieve n th row

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sunish

New User


Joined: 23 May 2005
Posts: 19

PostPosted: Thu Dec 08, 2005 4:33 pm    Post subject: how to retrieve n th row
Reply with quote

hi

i want to know the query to retrieve the n row of a table.i know that select * from table name fetch first 1 row only but this will work only in update version of db2 .pl
Back to top
View user's profile Send private message

venkatarao

New User


Joined: 19 Dec 2004
Posts: 24
Location: hyderabad

PostPosted: Thu Dec 08, 2005 6:07 pm    Post subject: Re: how to retrieve n th row
Reply with quote

if it is db2


select * from tab1 where n=(select count(*) from tab1)


if it is in oracle

select * from tab1 where rowid=n , it may work in sql server or oracle.
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Thu Dec 08, 2005 6:25 pm    Post subject: Re: how to retrieve n th row
Reply with quote

venkatarao,

Quote:
if it is db2

select * from tab1 where n=(select count(*) from tab1)

Would you like to explain this query on how its going to do.
Plz test once.

Regards,

Priyesh.
Back to top
View user's profile Send private message
dandapani

New User


Joined: 13 Dec 2005
Posts: 7
Location: banglore

PostPosted: Wed Dec 14, 2005 6:25 pm    Post subject: Please advise
Reply with quote

venkatarao ,

I guess query <select * from tab1 where n=(select count(*) from tab1) >

given by you will not work for HIS / HER question.

please correct me , if Iam wrong.

Regards,
Dhans
Back to top
View user's profile Send private message
iknow

Active User


Joined: 22 Aug 2005
Posts: 413
Location: Colarado, US

PostPosted: Wed Dec 14, 2005 8:50 pm    Post subject: Re: how to retrieve n th row
Reply with quote

Hi Sunish,


NOTE

The below provided queries are tested using ORACLE and some using SQL.
Apply the same logic in DB2 as per your usage.

Check the answers,

Quote:
CASE1:Retrieving only the Nth row from a table


Possible solution:1

Code:
SELECT * FROM t1 a
   WHERE  n = (SELECT COUNT(rowid)
      FROM t1 b
      WHERE a.rowid >= b.rowid);


Note: In this first query we select one more than the required row number, then we select the required one. Its far better than using a MINUS operation.

Possible solution:2

Code:

SELECT * FROM (
       SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 )
    WHERE  RN = 100;


Possible solution:3

Code:
SELECT f1 FROM t1
   WHERE  rowid = (
      SELECT rowid FROM t1
      WHERE  rownum <= 10
      MINUS
      SELECT rowid FROM t1
      WHERE  rownum < 10);


Code:
SELECT rownum,empno FROM scott.emp a
   GROUP BY rownum,empno HAVING rownum = 4;


Alternatively...


Code:
SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN
           (SELECT rowid FROM emp WHERE rownum < 10);


Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.

Quote:
CASE2:To select EVERY Nth row from a table


One can easily select all even, odd, or Nth rows from a table using SQL queries like this:

Method 1: Using a subquery

Code:
  SELECT *
        FROM   emp
        WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
                             FROM   emp);


Method 2: Use dynamic views (available from Oracle7.2):

Code:
SELECT *
        FROM   ( SELECT rownum rn, empno, ename
                 FROM emp
               ) temp
        WHERE  MOD(temp.ROWNUM,4) = 0;


Method 3: Using GROUP BY and HAVING

Code:
SELECT rownum, f1
   FROM t1
   GROUP BY rownum, f1 HAVING MOD(rownum,n) = 0 OR rownum = 2-n


Quote:
CASE3:To select the TOP N rows from a table


Form Oracle8i one can have an inner-query with an ORDER BY clause. Look at this example:

Code:
SELECT *
        FROM   (SELECT * FROM my_table ORDER BY col_name_1 DESC)
        WHERE  ROWNUM < 10;



Use this workaround with prior releases:

Code:
 SELECT *
          FROM my_table a
         WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
                        FROM my_table b
                       WHERE b.maxcol >= a.maxcol)
         ORDER BY maxcol DESC;
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Wed Dec 14, 2005 9:00 pm    Post subject: Re: how to retrieve n th row
Reply with quote

iknow...

Quote:
The below provided queries are tested using ORACLE and some using SQL.
Apply the same logic in DB2 as per your usage.

Would you like to apply these ORACLE logics in DB2 & come up with an exact query to find out nth row of a table.

Regards,

Priyesh.
Back to top
View user's profile Send private message
iknow

Active User


Joined: 22 Aug 2005
Posts: 413
Location: Colarado, US

PostPosted: Wed Dec 14, 2005 9:06 pm    Post subject: Re: how to retrieve n th row
Reply with quote

Hi Priyesh,

What I meant to say was apply the same logic with respect to syntax specific for DB2. Same processing but with some slight changes w.r.t DB2.

Hope I reached you.
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Wed Dec 14, 2005 9:23 pm    Post subject: Re: how to retrieve n th row
Reply with quote

Come on iknow... Its not an ORACLE forum.... The buddy asked query for a DB2 table...

I recall a similar incidence in this DB2 forum where query was asked to have functions in GROUP BY clause... From ORACLE point of view its a straight query... but in case of DB2, you cant map logic with ORACLE.
http://ibmmainframes.com/viewtopic.php?t=7128

Does any one over here find LOGIC similar to ROW NUM of ORACLE in DB2.

If yes, I would like to Thanks a lot... as I was in real need of such a logic ....

yeah, but the last query is without that Logic... That is to be tested on grounds.

Regards,

Priyesh.
Back to top
View user's profile Send private message
iknow

Active User


Joined: 22 Aug 2005
Posts: 413
Location: Colarado, US

PostPosted: Wed Dec 14, 2005 9:31 pm    Post subject: Re: how to retrieve n th row
Reply with quote

Hi Priyesh,

I strongly agree with your point.


To help you on this situation , you can visit this site www.sqlzoo.net

and try posting the same query in DB2 session and check what happens.

Let me also try and give you the result.

NOTE

In that website you can't refer like SYSIBM.SYSTABLES, it won't work.
Back to top
View user's profile Send private message
mijanurit
Currently Banned

New User


Joined: 26 Aug 2005
Posts: 33
Location: bangalore

PostPosted: Sun Jan 01, 2006 11:18 pm    Post subject: Re: how to retrieve n th row
Reply with quote

hi all,

i think it will work.

SELECT *
FROM (SELECT * FROM EMP_TAB
FETCH FIRST n ROWS ONLY)
FETCH LAST ROW ONLY

IF I am wrong plz correct me.


regards
mijanurit
Back to top
View user's profile Send private message
anadhamohan

New User


Joined: 27 Sep 2005
Posts: 25
Location: india

PostPosted: Wed Aug 16, 2006 2:59 pm    Post subject:
Reply with quote

Hopefully the below query should help:

SELECT A.NO FROM DBB.EMP A 10
WHERE 1 = (SELECT COUNT(*)
FROM DBB.EMP B WHERE B.NO >= A.NO);

thank you,
anadhamohan
Back to top
View user's profile Send private message
Muthukumar.PLindia

New User


Joined: 03 Jul 2006
Posts: 51
Location: Chennai

PostPosted: Fri Aug 18, 2006 9:44 pm    Post subject: Re: how to retrieve n th row
Reply with quote

hi mijanurit, will the Fetch Last row syntax work in 7.1....

Hi anandmohan, could you please explain your query...
Back to top
View user's profile Send private message
Sridevi_C

Active User


Joined: 22 Sep 2005
Posts: 104
Location: Concord, New Hampshire, USA.

PostPosted: Fri Aug 18, 2006 10:55 pm    Post subject: Re: how to retrieve n th row
Reply with quote

I understand the requirement is to write a QUERY in DB2.
But I could think of this cumbersome way...

Nth row of a table can be retrieved manually using COBOL-DB2 program.
Defining a cursor on the entire table and fetching all rows and just displaying the last row alone.

If the question was out of curiosity, I am sorry icon_smile.gif

Thanks.
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
No new posts Need to retrieve Julian_date Suganya87 DB2 4 Wed Aug 17, 2016 7:27 pm
No new posts Retrieve current year or month using ... vnktrrd DFSORT/ICETOOL 15 Tue Mar 15, 2016 4:14 pm
No new posts retrieve line commands from the data ... Pedro TSO/ISPF 6 Sat Oct 17, 2015 5:41 am
No new posts DFSORT - Retrieve selective records f... narasimha_devi DFSORT/ICETOOL 8 Thu Aug 06, 2015 4:12 pm
No new posts Retrieve records in the order of alte... jacobdng Compuware & Other Tools 8 Fri Jul 31, 2015 8:16 am


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