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:))
