【MySQL】GROUP BYした内容で優先順位をつける

Question

前提・実現したいこと

あるテーブル"a"が有り、
そのテーブルにカラム"a_id"、"status"が有ります。

テーブル"a"にレコードが以下の用に登録されていると仮定します。

a_id|status
1|1
2|2
3|3
4|4
5|2
6|1

テーブル"b"では以下のようにデータが入っています。
そのテーブルにカラム"b_id"、"hoge_id"、"a_id"が有ります。
b_id|hoge_id|a_id
1|1|1
2|1|2
3|1|3
4|1|4
5|2|5
6|2|6

※aに登録されているstatusの値は抽出する優先順位が有り、
4
2
1
3
という優先順位で表示するとなっています。

該当のクエリ

この時に以下のようにGROUP BYしてデータを抽出するとします。
statusに優先順位に沿った集計内容を表示がしたいです。

SELECT
    B.`hoge_id` AS hogeId
    ,MIN(A.`status`) AS status -- ここでstatusの優先順位に沿った集計内容を取得したい
FROM `a` AS A
INNER JOIN `b` AS B
ON A.a_id = B.a_id
GROUP BY B.hoge_id;

期待する結果

期待する結果は以下のようになればいいなと思います。
hogeId|status
1|4
2|2

補足情報

MySQLを使用しており、バージョンは5.7となります。
初めての質問で拙い点があるかと思いますが、宜しくお願い致します。

Answer 1

とりあえず無理やりやってみました。MySQL 8 ならウインドウ関数が使えるんですけどねぇ。
https://www.db-fiddle.com/f/uoj6g4riibCpLYUmzQEanN/0

テーブル定義

create table a (
  a_id int primary key,
  status int
);

insert into a values (1, 1);
insert into a values (2, 2);
insert into a values (3, 3);
insert into a values (4, 4);
insert into a values (5, 2);
insert into a values (6, 1);

create table b (
  b_id int primary key,
  hoge_id int,
  a_id int
);

insert into b values (1, 1, 1);
insert into b values (2, 1, 2);
insert into b values (3, 1, 3);
insert into b values (4, 1, 4);
insert into b values (5, 2, 5);
insert into b values (6, 2, 6);

問い合わせ

select
  b.hoge_id,
  case
    min(
      case a.status
        when 1 then 3
        when 2 then 2
        when 3 then 4
        when 4 then 1
      end
    )
    when 1 then 4
    when 2 then 2
    when 3 then 1
    when 4 then 3
  end as status
from a join b
  on a.a_id = b.a_id
group by b.hoge_id;

実行結果

+---------+--------+
| hoge_id | status |
+---------+--------+
|       1 |      4 |
|       2 |      2 |
+---------+--------+

Comments

[k.k.k.k]

ご回答いただきありがとうございます！
自分の方でも試したところ期待通りの結果を得ることができました。
この度はありがとうございます！