MySQL - Get MAX value per category, joining three tables

EDIT

Setting mysql config to this to match live server worked, I got error #1055 when not including selected columns in the GROUP BY statement

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

New query:

SELECT
*,
    c.name AS coin_name,
    c.coin_symbol AS coin_symbol,
    p.name AS platform_name,
    o.apy as apy,
    o.apy_note1 as apy_note1,
    o.apy_note2 as apy_note2,
    o.compounding_freq as compounding_freq,
    o.payout_freq as payout_freq,
    o.id as offer_id,
    
    
    MAX(apy) max_apy
FROM
    offers o
    INNER JOIN coins c
        ON c.id = o.coin_id
    INNER JOIN platforms p
        ON p.id = o.platform_id 
WHERE MATCH (c.name, c.coin_symbol) AGAINST ("'.$search_param.'" IN BOOLEAN MODE)
GROUP BY
    coin_name

Long time lurker, first time poster.

I have these three tables, and I'd like to get the maximum apy from the offers table, per coin while also displaying the platform name, showing only the highest apy for each coin.

Any help would be greatly appreciated.

Expected result:

+--------------+-------------+---------------+-----+
| coin_name    | coin_symbol | platform_name | apy |
+--------------+-------------+---------------+-----+
| Bitcoin      | BTC         | Platform 1    | 5%  |
+--------------+-------------+---------------+-----+
| Crypto.com   | CRO         | Platform 3    | 6%  |
+--------------+-------------+---------------+-----+
| Bitcoin Cash | BCH         | Platform 8    | 1%  |

Truncated tables:

CREATE TABLE `coins` (
`id` int(11) NOT NULL,
`name` varchar(128) NOT NULL,
`coin_symbol` varchar(200) NOT NULL,
`description` varchar(2000) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `platforms` (
`id` int(11) NOT NULL,
`name` varchar(200) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `offers` (
`id` int(11) NOT NULL,
`platform_id` int(11) DEFAULT NULL,
`coin_id` int(11) DEFAULT NULL,
`apy` varchar(10) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The query which gives duplicate coins

 SELECT
c.name AS coin_name,
c.coin_symbol AS coin_symbol,
p.name AS platform_name,
o.apy as apy,


MAX(apy) max_apy
FROM
offers o
INNER JOIN coins c
    ON c.id = o.coin_id
INNER JOIN platforms p
    ON p.id = o.platform_id

WHERE MATCH (c.name, c.coin_symbol) AGAINST ("bch,atom,cel,bnb,btc%" IN BOOLEAN MODE)

GROUP BY
coin_name, platform_name, coin_symbol, apy
ORDER BY `max_apy`  ASC

I'm thinking the "GROUP BY" clause is causing the issue, do I need to perform a subquery to group by coins first?

from Recent Questions - Stack Overflow https://ift.tt/3kFwiyA
https://ift.tt/eA8V8J