|
View previous topic :: View next topic
|
| Author |
Message |
sunish
New User
Joined: 23 May 2005
Posts: 19
|
|
|
|
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 |
|
 |
venkatarao
New User
Joined: 19 Dec 2004
Posts: 23
Location: hyderabad
|
|
|
|
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 |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
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 |
|
|
dandapani
New User
Joined: 13 Dec 2005
Posts: 7
Location: banglore
|
|
|
|
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 |
|
|
iknow
Active User
Joined: 22 Aug 2005
Posts: 411
Location: Colarado, US
|
|
|
|
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 |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
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 |
|
|
iknow
Active User
Joined: 22 Aug 2005
Posts: 411
Location: Colarado, US
|
|
|
|
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 |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
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 |
|
|
iknow
Active User
Joined: 22 Aug 2005
Posts: 411
Location: Colarado, US
|
|
|
|
Hi Priyesh,
I strongly agree with your point.
To help you on this situation , you can visit this site Use [URL] BBCode for External Links
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 |
|
|
mijanurit
Currently Banned
New User
Joined: 26 Aug 2005
Posts: 33
Location: bangalore
|
|
|
|
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 |
|
|
anadhamohan
New User
Joined: 27 Sep 2005
Posts: 25
Location: india
|
|
|
|
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 |
|
|
Muthukumar.PLindia
New User
Joined: 03 Jul 2006
Posts: 51
Location: Chennai
|
|
|
|
hi mijanurit, will the Fetch Last row syntax work in 7.1....
Hi anandmohan, could you please explain your query... |
|
| Back to top |
|
|
Sridevi_C
Active User
Joined: 22 Sep 2005
Posts: 104
Location: Concord, New Hampshire, USA.
|
|
|
|
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 
Thanks. |
|
| Back to top |
|
|
|
|
