RE: Mysql Query

[Steve Forsyth]

I am interested in seeing what the answer is to this question also,

This will not work as this only returns the top row of the result set.

The original question, i believe, was asking for the top count within each 
month which would basically give you the top day of each month.

Steve


>From: JR Ruggentaler <jr@stripped>
>To: "'Andy'" <andyckm@stripped>, java@stripped
>Subject: RE: Mysql Query
>Date: Wed, 21 May 2003 20:19:18 -0500
>
>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:))
>
>
>
>
>
>--
>MySQL Java Mailing List
>For list archives: http://lists.mysql.com/java
>To unsubscribe:    
>http://lists.mysql.com/java?unsub=1
>

_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*  
http://join.msn.com/?page=features/junkmail