|
View previous topic :: View next topic
|
| Author |
Message |
dandapani
New User
Joined: 13 Dec 2005
Posts: 7
Location: banglore
|
|
|
|
Hi ,
Need query for selecting records from 20 to 30 rows in a table .
It would be a great ,if you could post queries as soon as possible.
Regards,
Dhans |
|
| Back to top |
|
 |
iknow
Active User
Joined: 22 Aug 2005
Posts: 411
Location: Colarado, US
|
|
|
|
Hi dandapani,
NOTE
Queries are tested using ORACLE and SQL. In certain cases some queries won't work properly. So apply the same logic in DB2.
Check the answers,
Solution1:
Retrieving only rows X to Y from a table
In your case x=20,y=30.
| Code: |
SELECT * FROM (
SELECT ename, rownum rn
FROM emp WHERE rownum < 31
) WHERE RN between 21 and 30 ; |
Note: the 31 is just one greater than the maximum row of the required rows (means x= 20, y=30, so the inner values is y+1).
Solution2:
| Code: |
SELECT rownum, f1 FROM t1
GROUP BY rownum, f1 HAVING rownum BETWEEN 21 AND 30; |
Solution3:
Another solution is to use the MINUS operation. For example, to display rows 20 to 30, construct a query like this:
| Code: |
SELECT *
FROM tableX
WHERE rowid in (
SELECT rowid FROM tableX
WHERE rownum <= 30
MINUS
SELECT rowid FROM tableX
WHERE rownum < 20); |
Solution4:
"This one is faster and allowed for sorting before filtering by rownum. The inner query (table A) can be a series of tables joined together with any operation before the filtering by rownum is applied."
| Code: |
SELECT *
FROM (SELECT a.*, rownum RN
FROM (SELECT *
FROM t1 ORDER BY key_column) a
WHERE rownum <=30)
WHERE rn >=20 |
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.
Hope this helps. |
|
| Back to top |
|
|
dandapani
New User
Joined: 13 Dec 2005
Posts: 7
Location: banglore
|
|
|
|
Hi Iknow ,
I don't thing so that pre-defined function rownum exixts in DB2 ....
We tried , its giving error not a valid function.
Please let me know if you have any other solution in DB2
Regards,
Dhans. |
|
| Back to top |
|
|
iknow
Active User
Joined: 22 Aug 2005
Posts: 411
Location: Colarado, US
|
|
|
|
Hi dandapani,
Try with ROWID instead of ROWNUM.
I will try to give the solution to your query. |
|
| Back to top |
|
|
anilbatta
New User
Joined: 13 Nov 2005
Posts: 22
|
|
|
|
Hi
The following is the query in which it extracts only 10 rows between 20 and 30 rows.
SELECT * FROM STUDENT WHERE ROWID IN (SELECT ROWID FROM STUDENT WHERE ROWNUM <=30 MINUS SELECT ROWID FROM STUDENT WHERE ROWNUM >20);
Try with this code and let me know if any questions.Today i tried with this code and it is the efficient code to retrieve the number of rows. |
|
| Back to top |
|
|
nikyojin
New User
Joined: 05 Oct 2005
Posts: 94
|
|
|
|
Hi Anibatta,
I tried the same query with some modifications as mentioned below :-
| Quote: |
SELECT CR_STATUS_DATE
FROM HUM.A_CRSTT
WHERE ROWID IN
(SELECT ROWID
FROM HUM.A_CRSTT
WHERE ROWNUM > 20
AND ROWNUM <= 30) |
I'm getting the error
| Quote: |
SQLCODE = -206, ERROR: ROWID IS NOT A COLUMN OF AN INSERTED
TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM
CLAUSE, OR IS NOT A COLUMN OF THE TRIGGERING TABLE OF A
TRIGGER |
RowID is not recognized in DB2.Pls give the soln if possible.... |
|
| Back to top |
|
|
khamarutheen
Active Member
Joined: 23 Aug 2005
Posts: 677
Location: NJ
|
|
|
|
Hi nikyojin,
Rowid is not a system defined column in DB2 it is in oracle only. so i dont think u can get solved by the above. try with ur primary key it will work. let me know if u r still on confusion |
|
| Back to top |
|
|
nikyojin
New User
Joined: 05 Oct 2005
Posts: 94
|
|
|
|
Hi khamarutheen,
Thanks for the reply...I was just confused cause of the "Row-ID" col.
Regards,
Nikhil .S. |
|
| Back to top |
|
|
khamarutheen
Active Member
Joined: 23 Aug 2005
Posts: 677
Location: NJ
|
|
|
|
Hi nikyjin,
Nothing to get confuse yar. b clear v dont have rowid or rowcol in DB2 as like in oracle. so only v use FETCH option to fetch first 10 rows or last 10 rows like that. else use BETWEEN with the help of primary key inorder to fetch the particular range of rows. |
|
| Back to top |
|
|
mijanurit
Currently Banned
New User
Joined: 26 Aug 2005
Posts: 33
Location: bangalore
|
|
|
|
hi dandapani and other experts,
i am trying to give the sql statement of the above query.
if i am wrong plz correct me.
let ur table name EMP_TAB contains 100 rows.
SELECT *
FROM (SELECT * FROM EMP_TAB
FETCH FIRST 30 ROWS ONLY)
FETCH LAST 10 ROWS ONLY
plz tell me whether i am correct or wrong.
regards
mija |
|
| Back to top |
|
|
ANIKET
New User
.jpg)
Joined: 28 Apr 2005
Posts: 11
Location: PUNE
|
|
|
|
Mija
I dont think DB2 supports "LAST" verb in this sense. Although it is good suggestion. |
|
| Back to top |
|
|
nikyojin
New User
Joined: 05 Oct 2005
Posts: 94
|
|
|
|
| Great Soln Mija...smart thinking...but DB2 doesn't support "Fetch first n rows only" in the subquery |
|
| Back to top |
|
|
|
|
