MySQL output gives values from different rows

I am working on sql and there are 4 different columns which are pname, Fname,Flastname and amount. It gives the correct outputs for pname and amount but fname and flastname are from different rows which are not related to the amount and pname. The query should output the following question: Find the names of farmers who sells most for each product in the website.

I have 2 attributes which are farmers and buys:

buys: 
    Fname;Flastname;pname;mname;maddress;amount;creditcard;
    Cevdet;Sututan;Grain;Bim;Ilginc sk;18000;1230000000000010;
    Cevdet;Sututan;Soy bean;Migros;Zengin Mh;5000;1230000000000050;
    Halime;Sututan;Grain;Bim;Cikmaz Sk;1000;1230000000000340;
    Muhammer;Kanzuk;Soy bean;Migros;Zengin Mh;34000;1230000000002400;
    Gulden;Kanzuk;Grain;Tansas;Seftali Mh;1000;1230000000016810;
    Gulden;Kanzuk;Soy bean;KIPA;Boyoz Sk;7000;1230000000117650;



farmers:
    name;lastname;address;zipcode;city;phones;e-mails
    Cevdet;Sututan;14 Cikmaz Sk;42050;Konya;5352223444;cevdet@sucu.com
    Halime;Sututan;14 Cikmaz 
    Sk;42050;Konya;5356823445|5352223456;halime@sucu.com
    Muhammer;Kanzuk;16 Genis 
    Sk;34052;Istanbul;5352223446;muhammer@sucu.com
    Gulden;Kanzuk;16 Genis 
    Sk;34052;Istanbul;5352223447;gilden@sucu.com|gilden2@sucu.com
    Murat;Kanzuk;16 Genis Sk;34052;Istanbul;5352223448;asd@sucu.com





expected output:
        Grain;Hanedan;Gercek
        Soy bean;Muhammer;Kanzuk
        Rice;Behcet;Karci
        Corn;Deniz;Suadiyeli

my sql code is below:

        ``` 
                Select B.pname, B.Fname, B.Flastname, max(B.amount)
                from buys B,
                     farmers F
                where (B.Flastname=F.lastname and F.name = B.Fname) 
                group by B.pname ;
       ```

Answer

Get the max amount per product in a sub-query and join against that query

SELECT b.pname, Fname, Flastname, max_amount
FROM buys b
JOIN (SELECT pname, MAX(amount) AS max_amount FROM buys GROUP BY pname) m ON m.pname = b.pname AND m.max_amount = b.amount

Advertisement

Answer