Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
query to get count between two dates

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

New User


Joined: 21 Apr 2010
Posts: 10
Location: Hydearabad

PostPosted: Thu Jul 22, 2010 12:03 pm    Post subject: query to get count between two dates
Reply with quote

Hi,

i have a table with two fields as below

date count
2010-07-19 302
2010-06-15 356
2010-07-15 145
2009-09-19 401
2010-04-16 058
2009-06-17 500
2009-08-12 128
2009-08-14 600
....
...
..
I want to retrieve first 5 rows with count>300 in the last year(between 2010-07-19 and 2009-07-19).how can i write a query??
expected o/p is
2009-08-14 600
2009-09-19 401
2010-06-15 356
2010-07-19 302

thanks inadvance.
Back to top
View user's profile Send private message

PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2442
Location: Netherlands, Amstelveen

PostPosted: Thu Jul 22, 2010 12:12 pm    Post subject:
Reply with quote

What did you try yourself?
Back to top
View user's profile Send private message
sai_sridar_s

New User


Joined: 14 Nov 2006
Posts: 6

PostPosted: Thu Jul 22, 2010 12:27 pm    Post subject: Reply to: query to get count between two dates
Reply with quote

Hi.. i didnt check the output, but hope this should work.

select date1, count1
from table1
where date1 between '2009-07-19' and '2010-07-19'
order by count1 desc
having count1 > 300
FETCH FIRST 5 ROWS ONLY

pls check and tell whether it works.
Hope my understanding is correct.
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 Jul 22, 2010 7:10 pm    Post subject:
Reply with quote

Hello,

Why order by count when the requested output is in order by date, not count?

Quote:
i didnt check the output, but hope this should work.
If you had checked the output, you might have seen that the result did not match the request. . .
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Fri Jul 23, 2010 1:08 am    Post subject: Reply to: query to get count between two dates
Reply with quote

Hi

dick scherrer wrote

Quote:
Why order by count when the requested output is in order by date, not count?


As per the requested output the date is in ascending order and the count is in descending order. I think the replay by sai_sridar_s will yield the expected result.


Code:
 SELECT * FROM FORUM1 WHERE DATE1 BETWEEN '2009-07-19' AND '2010-07-19'
AND COUNT > 300 ORDER BY COUNT DESC FETCH FIRST 5 ROWS ONLY;           
---------+---------+---------+---------+---------+---------+---------+-
DATE1        COUNT                                                     
---------+---------+---------+---------+---------+---------+---------+-
2009-08-14     600                                                     
2009-09-19     401                                                     
2010-06-15     356                                                     
2010-07-19     302                                                     
DSNE610I NUMBER OF ROWS DISPLAYED IS 4                                 
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100



If I am missing something, please correct me.

Regards
Raghu
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: Fri Jul 23, 2010 1:15 am    Post subject:
Reply with quote

Hello,

Quote:
If I am missing something, please correct me.
The order appears correct because the test data is insufficient. . .

If there were more rows with more dates and counts (that are not in order), the sequence of the output would no longer be what is wanted.

If a column is not specified in the order by, there is no way to predict the sequence of the values of that column.

Possibly there is something i'm missing. . .
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Jul 23, 2010 1:36 am    Post subject:
Reply with quote

dick scherrer wrote:
Possibly there is something i'm missing. . .


not a thing my (IMUO - overly) diplomatic friend,
Raghu navaikulam does not know what he is talking about.

as you said, if the column is not in the ORDER BY, no way to predict.
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 193
Location: chennai

PostPosted: Sat Jul 24, 2010 10:41 pm    Post subject: Reply to: query to get count between two dates
Reply with quote

Thank you
dick scherrer and dbzTHEdinosauer
for pointing out my mistake....
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: Sun Jul 25, 2010 3:10 am    Post subject: Reply to: query to get count between two dates
Reply with quote

You're welcome icon_smile.gif

When there are more questions, someone will be here. . .

d
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 How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us