have you tried
select issue_date, count(*)
from user_email_details
where news_letter_type = 'SiNews:H'
group by issue_date
order by count(*) desc
limit 1;
J.R.
-----Original Message-----
From: Andy [mailto:andyckm@stripped]
Sent: Wednesday, May 21, 2003 7:23 PM
To: java@stripped
Subject: Mysql Query
hi,
Currently i have this tble called "A"
with fields :
----------------------------------------------------------------------------
---
urlclicked ----- varchar(150) PRI
nooftimesclicked ---- int(11)
issue_date ----- date
news_letter_type ----- varchar(25)
date_url_clicked ----- date PRI
issue_end_date ----- date
email_address ----- varchar(60) PRI
----------------------------------------------------------------------------
-----
For data pertaining to the issue_date, the following (an exerpt of the
actual data) shows
up when i do this query:
"select issue_date, count(*) from user_email_details where news_letter_type
= 'SiNews:H' group by issue_
date;"
issue_date | count(*) |
2002-07-02 | 80
2002-07-09 | 200
2002-07-14| 1000
### this is max for july
2002-08-09 | 75
2002-08-17 | 95
2002-08-30 | 3000
### this is max for aug
2002-09-16 | 156
### this is max for sep
2002-09-08 | 45
2002-09-05 | 74
However, what i require is the max count and the particular date of the
month to show up instead , which is:
issue_date | count(*)
2002-07-14 | 1000
2002-08-30 | 3000
2002-09-16 | 156
Does anyone have any idea how to achieve this in as direct or simple a query
possible for mysql?
i have tried using max(count(*)), group by left(issue_date,7) but to no
avail.
another column which serves the same purpose would be the
sum(noofurlclicked), but max cannot be used after the sum is done
(i am actually using this query inside one of my select statement in my jsp
page, and mysql version is 3.23.54)
thanks for your help:))
