thanks I worked it out. SQLServer requires SELECT Count(*) as fieldname ...
ORDER BY Count(*). MySQL requires SELECT count(*) as fieldname ... ORDER BY
fieldname
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Fred van Engen
Sent: Wednesday, 21 March 2001 10:54
To: Daniel Kirk; [EMAIL PROTECTED]
Subject: Re: group by error in query
On Wed, Mar 21, 2001 at 07:31:20PM +1100, Daniel Kirk wrote:
> Hi,
>
> I've just ported from SQLServer where this query ran fine, however MySQL
> gives an "invalid use of group function" error.
>
> Any ideas?
>
> thx
>
> dan
>
> select us.username, us.id as userid, count(*) as tips from
UserTippingInfo
> u, Matches m, Users us WHERE round = 23 AND us.id = u.userid AND
> m.matchnumber = u.matchnumber and played = 1 and u.tip = m.winner and
> u.competitionid = 20 and datemade < dateplayed group by us.username, us.id
> order by count(*) DESC, MIN(datemade) LIMIT 10
>
What version of MySQL are you using? Versions before 3.23.xx don't allow
group functions in the order by clause. See the manual for a workaround.
I believe you would name the expressions in your field list and then use
the name in your order by.
Regards,
Fred.
--
Fred van Engen XO Communications B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
