|
View previous topic :: View next topic
|
| Author |
Message |
Pavani G
New User
Joined: 21 Apr 2010
Posts: 10
Location: Hydearabad
|
|
|
|
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 |
|
 |
PeterHolland
Global Moderator
Joined: 27 Oct 2009
Posts: 2481
Location: Netherlands, Amstelveen
|
|
|
|
| What did you try yourself? |
|
| Back to top |
|
|
sai_sridar_s
New User
Joined: 14 Nov 2006
Posts: 5
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Thank you
dick scherrer and dbzTHEdinosauer
for pointing out my mistake.... |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
You're welcome 
When there are more questions, someone will be here. . .
d |
|
| Back to top |
|
|
|
|
