Brighthouse group_concat alternative?

Question

I'm working with a large database where we've got some key queries that rely on group_concat (via myisam) to correctly order multiple columns when grouped. Essentially, so that we make sure to get the correct data in each row. Example if I wanted to know how many records I have in a particular state that are in each zip code, but only one record per address:

(id is a unique unsigned number)

SELECT state     AS state,
       zip       AS zip,
       COUNT(id) AS total
FROM   (SELECT CAST(Substring_index(GROUP_CONCAT(id ORDER BY state, zip, city,
               address,
                      id),
               ',', 1)
               AS UNSIGNED)   AS id,
               CAST(Substring_index(GROUP_CONCAT(zip ORDER BY state, zip, city,
               address
                      , id),
               ',', 1
               ) AS UNSIGNED) AS zip,
               CAST(Substring_index(GROUP_CONCAT(state ORDER BY state, zip, city
               ,
                      address, id),
               ',',
               1) AS CHAR)    AS state
        FROM   DATABASE.table
        GROUP  BY st,
                  zip,
                  city,
                  address) AS a
GROUP  BY zip
ORDER  BY zip  

I have a myisam and an brighthouse database with identical data to play with. If I'm just doing simple selects obviously brighthouse has a HUGE performance advantage, so I'm hoping to find a way to run a query with identical results that can take advantage of the brighthouse speed.

Edit: To correct myself, I would like to find a query that accomplishes this same thing with the Brighthouse engine, not innodb as I previously stated.

Answer 1

I eventually figured out I could rearrange some things to get what I was after. The query in my original question can be re-written as:

SELECT state     AS state,
       zip       AS zip,
       COUNT(id) AS total
FROM   (SELECT state,
               zip,
               id
        FROM   (SELECT state,
                       zip,
                       id,
                       address
                FROM   DATABASE.table
                ORDER  BY id) AS a
        GROUP  BY address) AS a2
GROUP  BY zip
ORDER  BY zip  

...which allowed the same result set and took advantage of the Brighthouse engine! :-)