I've tried it and it doesn't produce an error. The program I'm converting has all sorts of other bugs to clear before I can check the results (oh the fun of going from Access/CF4.0 to MYSQl/CFMX, added to a new database design which is closer to proper normality).
But a question - I'm looking for the top value only. Won't the below simply show all values in DESC order ? And I was using DISTINCT in the previous version as well. > TRY > SELECT DISTINCT Postcode, Count(*) AS PopSubCount > ........... > GROUP BY Postcode > ORDER BY PopSubCount DESC > > > PS does anyone know of a good mySQL list in or near this timezone?=20 > > ----- Original Message -----=20 > From: <[EMAIL PROTECTED]> > To: "CFAussie Mailing List" <[EMAIL PROTECTED]> > Sent: Friday, December 12, 2003 11:58 AM > Subject: [cfaussie] RE: Slight OT : MySQL Desc equivalent > > > just a stab in the dark, if I understand what your trying to do and = > MySQL supports it. > > MAX() > > PS. how about a MySQL mailing list, they would be more helpfull I guess? > > -----Original Message----- > From: Andrew Dickinson [mailto:[EMAIL PROTECTED] > Sent: Friday, 12 December 2003 10:52 AM > To: CFAussie Mailing List > Subject: [cfaussie] Slight OT : MySQL Desc equivalent > > > More MySQL conversion fun. > > I'm looking to convert this syntax to MySQL > > SELECT DISTINCT TOP 1 Postcode,=20 > Count(*) AS PopSubCount > ........... > GROUP BY Postcode > ORDER BY Count(*) DESC > > I've worked out that=20 > "TOP 1" under select=20 > needs to be replaced by > "LIMIT 1" at the bottom. > > But what to do with the Count(*) DESC ? > > I have tried=20 > LIMIT 1 > ORDER BY Count(*) DESC > > and > > ORDER BY Count(*) DESC > LIMIT 1 > > But they both come out as syntax errors. > > Briefly, the purpose of this code is to extract the postcode where the > largest number of customers (that's what Count DESC is used for - to = > find > the largest number) are living, and then break it down for further > deomgraphic stats. > > > --- > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > To unsubscribe send a blank email to = > [EMAIL PROTECTED] > > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia > http://www.mxdu.com/ + 24-25 February, 2004 > > --- > You are currently subscribed to cfaussie as: = > [EMAIL PROTECTED] > To unsubscribe send a blank email to = > [EMAIL PROTECTED] > > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia > http://www.mxdu.com/ + 24-25 February, 2004 > > ------=_NextPart_000_13E2_01C3C0AA.0F7699B0 > Content-Type: text/html; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> > <HTML><HEAD> > <META http-equiv=3DContent-Type content=3D"text/html; = > charset=3Diso-8859-1"> > <META content=3D"MSHTML 6.00.2800.1226" name=3DGENERATOR> > <STYLE></STYLE> > </HEAD> > <BODY> > <DIV><FONT face=3DArial size=3D2>TRY</FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2>SELECT DISTINCT Postcode, = > Count(*) AS=20 > PopSubCount<BR>...........<BR>GROUP BY Postcode<BR>ORDER BY=20 > <STRONG>PopSubCount</STRONG> DESC</FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2>PS does anyone know of a good mySQL = > list in or near=20 > this timezone? </FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2>----- Original Message ----- </FONT> > <DIV><FONT face=3DArial size=3D2>From: <</FONT><A=20 > href=3D"mailto:[EMAIL PROTECTED]"><FONT = > face=3DArial=20 > size=3D2>[EMAIL PROTECTED]</FONT></A><FONT = > face=3DArial=20 > size=3D2>></FONT></DIV> > <DIV><FONT face=3DArial size=3D2>To: "CFAussie Mailing List" = > <</FONT><A=20 > href=3D"mailto:[EMAIL PROTECTED]"><FONT face=3DArial=20 > size=3D2>[EMAIL PROTECTED]</FONT></A><FONT face=3DArial=20 > size=3D2>></FONT></DIV> > <DIV><FONT face=3DArial size=3D2>Sent: Friday, December 12, 2003 11:58=20 > AM</FONT></DIV> > <DIV><FONT face=3DArial size=3D2>Subject: [cfaussie] RE: Slight OT : = > MySQL Desc=20 > equivalent</FONT></DIV></DIV> > <DIV><FONT face=3DArial><BR><FONT size=3D2></FONT></FONT></DIV><FONT = > face=3DArial=20 > size=3D2>just a stab in the dark, if I understand what your trying to do = > and MySQL=20 > supports it.<BR><BR>MAX()<BR><BR>PS. how about a MySQL mailing list, = > they would=20 > be more helpfull I guess?<BR><BR>-----Original Message-----<BR>From: = > Andrew=20 > Dickinson [mailto:[EMAIL PROTECTED]<BR>Sent: Friday, 12 = > December 2003=20 > 10:52 AM<BR>To: CFAussie Mailing List<BR>Subject: [cfaussie] Slight OT : = > MySQL=20 > Desc equivalent<BR><BR><BR>More MySQL conversion fun.<BR><BR>I'm looking = > to=20 > convert this syntax to MySQL<BR><BR>SELECT DISTINCT TOP 1 Postcode, = > <BR>Count(*)=20 > AS PopSubCount<BR>...........<BR>GROUP BY Postcode<BR>ORDER BY Count(*)=20 > DESC<BR><BR>I've worked out that <BR>"TOP 1" under select <BR>needs to = > be=20 > replaced by<BR>"LIMIT 1" at the bottom.<BR><BR>But what to do with the = > Count(*)=20 > DESC ?<BR><BR>I have tried <BR>LIMIT 1<BR>ORDER BY Count(*)=20 > DESC<BR><BR>and<BR><BR>ORDER BY Count(*) DESC<BR>LIMIT 1<BR><BR>But they = > both=20 > come out as syntax errors.<BR><BR>Briefly, the purpose of this code is = > to=20 > extract the postcode where the<BR>largest number of customers (that's = > what Count=20 > DESC is used for - to find<BR>the largest number) are living, and then = > break it=20 > down for further<BR>deomgraphic stats.<BR><BR><BR>---<BR>You are = > currently=20 > subscribed to cfaussie as: </FONT><A = > href=3D"mailto:[EMAIL PROTECTED]"><FONT=20 > face=3DArial size=3D2>[EMAIL PROTECTED]</FONT></A><BR><FONT = > face=3DArial size=3D2>To=20 > unsubscribe send a blank email to </FONT><A=20 > href=3D"mailto:[EMAIL PROTECTED]"><FONT = > face=3DArial=20 > size=3D2>[EMAIL PROTECTED]</FONT></A><BR><BR><FONT= > =20 > face=3DArial size=3D2>MXDU2004 + Macromedia DevCon AsiaPac + Sydney,=20 > Australia<BR></FONT><A href=3D"http://www.mxdu.com/";><FONT face=3DArial=20 > size=3D2>http://www.mxdu.com/</FONT></A><FONT face=3DArial size=3D2> + = > 24-25 February,=20 > 2004<BR><BR>---<BR>You are currently subscribed to cfaussie as: = > </FONT><A=20 > href=3D"mailto:[EMAIL PROTECTED]"><FONT face=3DArial=20 > size=3D2>[EMAIL PROTECTED]</FONT></A><BR><FONT = > face=3DArial=20 > size=3D2>To unsubscribe send a blank email to </FONT><A=20 > href=3D"mailto:[EMAIL PROTECTED]"><FONT = > face=3DArial=20 > size=3D2>[EMAIL PROTECTED]</FONT></A><BR><BR><FONT= > =20 > face=3DArial size=3D2>MXDU2004 + Macromedia DevCon AsiaPac + Sydney,=20 > Australia<BR></FONT><A href=3D"http://www.mxdu.com/";><FONT face=3DArial=20 > size=3D2>http://www.mxdu.com/</FONT></A><FONT face=3DArial size=3D2> + = > 24-25 February,=20 > 2004<BR></FONT> ---<BR> You are currently subscribed to cfaussie as: [EMAIL PROTECTED]<BR> To unsubscribe send a blank email to [EMAIL PROTECTED] <BR> MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia<BR> http://www.mxdu.com/ + 24-25 February, 2004 </BODY></HTML> > > ------=_NextPart_000_13E2_01C3C0AA.0F7699B0--
