CREATE OR REPLACE VIEW grouped_latest_rates_view AS
SELECT
    res.ticker_id,
    res.suid,
    res.batch_id,
    value_date,
    res.mid_rate_id,
    (SELECT value FROM rateclient.rates r1 WHERE r1.id = res.mid_rate_id) AS mid_value

FROM (
SELECT
    (SELECT r1.value_date FROM rateclient.rates r1 JOIN rateclient.provider_tickers t1 ON r.ticker_id = t1.id LIMIT 1) AS value_date,
    r.ticker_id,
    t.suid,
    r.batch_id,

    -- SLOW QUERY
    (SELECT r1.id FROM rateclient.rates r1
    LEFT JOIN rateclient.provider_tickers t1 ON r1.ticker_id = t1.id
    LEFT JOIN fx_tickers ft1 ON t1.suid = ft1.ticker_suid
    LEFT OUTER JOIN rateclient.provider_fields f1 ON r1.field_id = f1.id
    WHERE t1.suid = t.suid AND r1.batch_id = r.batch_id AND ft1.mid_field_id = r1.field_id LIMIT 1) AS mid_rate_id

FROM
rateclient.rates r
LEFT JOIN rateclient.provider_tickers t ON r.ticker_id = t.id
LEFT JOIN fx_tickers ft ON t.suid = ft.ticker_suid
LEFT OUTER JOIN rateclient.provider_fields f ON r.field_id = f.id
GROUP BY r.ticker_id, r.batch_id, t.suid ORDER BY r.ticker_id) res;