View previous topic :: View next topic
|
Author |
Message |
devangi83
New User
Joined: 05 May 2009 Posts: 7 Location: India
|
|
|
|
Hi,
I want to fetch some records from table in my program. I have declared cursor with below sql query
SELECT GRP-ID FROM TEST WHERE GRP-ID < 20
ORDER BY GRP-ID DESC
FETCH FIRST 5 ROWS ONLY
the above query is giving me output as follows
GRP-ID
19
18
17
16
15
Is there any way i can get the records in reverse order? i.e
15,16,17,18,19
if i am removing DESC, it is fetching GRP-ID 1,2,3,4,5 which i don't want, i want output to be 15,16,17,18,19 |
|
Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
So you actually want the last five rows? |
|
Back to top |
|
|
devangi83
New User
Joined: 05 May 2009 Posts: 7 Location: India
|
|
|
|
yes.. and not in desc order.. |
|
Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
I'm not sure, but since there is a FETCH FIRST, isn't there a FETCH LAST? |
|
Back to top |
|
|
devangi83
New User
Joined: 05 May 2009 Posts: 7 Location: India
|
|
|
|
thts for scrollable cursor, which i am not using... |
|
Back to top |
|
|
CICS Guy
Senior Member
Joined: 18 Jul 2007 Posts: 2146 Location: At my coffee table
|
|
|
|
Where is "FETCH FIRST 5 ROWS ONLY" documented that it is not a "scrollable cursor"?
What is the diffefence between a "row" and a "rowset"? |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
I'm not sure where this thread is heading to -- why not just change this
Code: |
ORDER BY GRP-ID DESC |
to
Code: |
ORDER BY GRP-ID ASC |
in your query. . .
This will not meet the requirment. |
|
Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
|
|
Devan,
You can try the following query
SELECT A.GRP-ID FROM TEST A
WHERE 5 > (SELECT COUNT(*)
FROM TEST B
WHERE A.GRP-ID < B.GRP-ID
AND A.GRP-ID < 20) AND
A.GRP-ID < 20
ORDER BY A.GRP-ID; |
|
Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
|
|
Just a small correction.
SELECT A.GRP-ID FROM TEST A
WHERE 5 > (SELECT COUNT(*)
FROM TEST B
WHERE A.GRP-ID < B.GRP-ID
AND B.GRP-ID < 20) AND
A.GRP-ID < 20
ORDER BY A.GRP-ID; |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
What version of DB2 are you using devangi ?
Sushanth |
|
Back to top |
|
|
lineesh_kumar
New User
Joined: 20 Feb 2006 Posts: 6
|
|
|
|
Select GRP-ID FROM
(SELECT GRP-ID FROM TEST WHERE GRP-ID < 20
ORDER BY GRP-ID DESC
FETCH FIRST 5 ROWS ONLY)
ORDER BY GRP-ID asc;
Try this... |
|
Back to top |
|
|
Succor
New User
Joined: 20 Feb 2009 Posts: 96 Location: Bangalore :)
|
|
|
|
lineesh_kumar, do you think this will pass the precompilation step? |
|
Back to top |
|
|
MFRASHEED
Active User
Joined: 14 Jun 2005 Posts: 186 Location: USA
|
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Quote: |
This will not meet the requirment. |
I'm surely missing something. Please assist. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Anuj D. wrote: |
Quote: |
This will not meet the requirment. |
I'm surely missing something. Please assist. |
I am missing something; from where is the first quote?
not being a butt-head, but occasionally I see a post and then it is gone. |
|
Back to top |
|
|
devangi83
New User
Joined: 05 May 2009 Posts: 7 Location: India
|
|
|
|
Thanks Srihari,
Your query works.. but i'm sure my DBA wont allow it.. thnx anyways.... |
|
Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007 Posts: 295 Location: Singapore
|
|
|
|
Devan,
Let me know your DBA's comments.
Anuj,
if i am removing DESC, it is fetching GRP-ID 1,2,3,4,5 which i don't want, i want output to be 15,16,17,18,19.
This is from Devan's first post. I think this what you are missing. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hi Dick,
Anuj D. wrote:
Quote: |
Quote: |
This will not meet the requirment. |
I'm surely missing something. Please assist. |
Quote: |
I am missing something; from where is the first quote? |
The quote is in the "fine print" of the first reply from Anuj. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Thx Dick, |
|
Back to top |
|
|
|