Can I just ask a quick question about this one, mainly about the Group By statement and how its used in MySQL. It appears that you don't need to specify in the Group By clause all the columns that are referenced without an aggregate function i.e vendor is missed out from: GROUP BY p3.id, p3.price. I'm just asking because my background is MSSQL and you would have to specify vendor. For example this query would be invalid in MSSQL but valid in MySQL:
select id, vendor, sum(price) from p group by id But this one would work in both: select id, vendor, sum(price) from p group by id, vendor Each query giving a different result. Regards Ben. -----Original Message----- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: 10 August 2005 23:29 To: mysql@lists.mysql.com Subject: Re: HELP! sql command question for mysql Hello. I'm not sure about the speed of this query, but it seems to work: mysql> source g.sql +-------+-------+--------+ | id | price | vendor | +-------+-------+--------+ | OG012 | 20 | b | | OG013 | 40 | c | +-------+-------+--------+ [EMAIL PROTECTED] mysql-debug-5.0.10-beta-linux-i686]$ cat g.sql SELECT p4.id ,p4.price ,p4.vendor FROM ( SELECT p3.id ,p3.price ,p3.vendor ,MIN( p3.rating) FROM ( SELECT p1.id ,p1.price ,p1.vendor ,q1.rating FROM p p1 INNER JOIN q q1 ON p1.vendor = q1.vendor WHERE p1.price = ( SELECT MIN(p2.price) FROM p p2 WHERE p2.id = p1.id ) ) AS p3 GROUP BY p3.id, p3.price ) AS p4 ; See: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html Joe Culler <[EMAIL PROTECTED]> wrote: > Hello there, > > first of all, my english isn't good, hope you understand what I mean. > > I have a table name "p" like that: > > mysql> select * from p; > +-------+-------+--------+ > | id | price | vendor | > +-------+-------+--------+ > | OG012 | 40 | a | > | OG012 | 20 | b | > | OG012 | 20 | c | > | OG013 | 40 | c | > +-------+-------+--------+ > > and I have another table name q: > mysql> select * from q; > +--------+--------+ > | vendor | rating | > +--------+--------+ > | a | 1 | > | b | 2 | > | c | 3 | > +--------+--------+ > > My question is how do I find the minimal price for each id and vendor > rating is highest. I wish my result is: > > +-------+-------+--------+ > | id | price | vendor | > +-------+-------+--------+ > | OG012 | 20 | b | > | OG013 | 40 | c | > +-------+-------+--------+ > > Since vender b and c are the same price for id(OG012), but vendor b > has=20 minimal rating then vendor c. > > Many thanks, > Joe. > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ ============================================================================== For more information on Hargreaves Lansdown, visit our web site http://www.hargreaveslansdown.co.uk IMPORTANT NOTICE This email is intended solely for the recipient and is confidential and not for third party unauthorised distribution. If an addressing, or transmission, error has misdirected this email, please notify the author by replying to this email or notifying the system manager ([EMAIL PROTECTED]) if you are not the intended recipient you must not disclose, distribute, copy, print or rely on this email. Any opinions expressed in this document are those of the author and do not necessarily reflect the opinions of Hargreaves Lansdown. In addition, staff are not authorised to enter into any contract through email and therefore nothing contained herein should be construed as such. This email has been prepared using information believed by the author to be reliable and accurate but Hargreaves Lansdown makes no warranty as to the accuracy or completeness. In particular, Hargreaves Lansdown does not accept responsibility for any changes made to this email after it was sent. All group companies are Authorised and regulated by the Financial Services Authority and registered in England and the registered office is Kendal House, 4 Brighton Mews, Clifton, Bristol, BS8 2NX. Telephone: 0117 9889880 ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
