How to force GROUP BY and subquery in Mysql to return desired table variant?

Question

MySQL

Hello. The essence of the question I want to get a table of the following form:
___________________________________________________________________________
| NCValue | NCValue_sale | value_buy | value_sale | id_name_banks_buy |
------------|-----------------|------|---- ---------|--------------------------------|
We make a query to the database and get value_buy , then this value_buy needs to be found in another table and get its id_name_banks and insert it into the id_name_banks_buy column.
Table structure.
Something does not want to upload pictures, so I attach from Yandex disk.

mysql> show create table parser_all_exchange_rates \G;
*************************** 1. row ***************************
       Table: parser_all_exchange_rates
Create Table: CREATE TABLE `parser_all_exchange_rates` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `id_name_banks` int NOT NULL,
  `id_name_currency` int NOT NULL,
  `value` decimal(6,3) NOT NULL DEFAULT '0.000',
  `value_sale` decimal(6,3) NOT NULL DEFAULT '0.000',
  `date` date NOT NULL,
  `time` time NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `FK_parser_all_exchange_rate_parser_name_banks` (`id_name_banks`) USING BTREE,
  KEY `FK_parser_all_exchange_rate_parser_name_currency` (`id_name_currency`) USING BTREE,
  CONSTRAINT `FK_parser_all_exchange_rate_parser_name_currency` FOREIGN KEY (`id_name_currency`) REFERENCES `parser_name_currencies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=770 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> show create table parser_name_banks \G;
*************************** 1. row ***************************
       Table: parser_name_banks
Create Table: CREATE TABLE `parser_name_banks` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name_bank_ru` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name_bank_en` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_bank_ru` (`name_bank_ru`)
) ENGINE=InnoDB AUTO_INCREMENT=235 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> show create table parser_name_currencies \G;
*************************** 1. row ***************************
       Table: parser_name_currencies
Create Table: CREATE TABLE `parser_name_currencies` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name_currency_en` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_currency_ru` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `unit` smallint NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

Requests

SELECT
   e.name_currency_en AS NCValue,
   e.name_currency_en AS NCValue_sale,
   MAX(c.value) value, 
   MIN(c.value_sale) value_sale
FROM 
   parser_all_exchange_rates c
INNER JOIN 
   parser_name_currencies e ON e.id = c.id_name_currency
   WHERE c.date = CURRENT_DATE() 
   AND c.id_name_banks != 233 
   AND value_sale != 0
GROUP BY NCValue, NCValue_sale;

This is the first query, it gives what you need, sorting by MAX and MIN works. Now I am attaching to the id_name_banks_buy request.

SELECT
   e.name_currency_en AS NCValue,
   e.name_currency_en AS NCValue_sale,
   MAX(c.value) value, 
   MIN(c.value_sale) value_sale,
   c.id_name_banks
FROM 
   parser_all_exchange_rates c
INNER JOIN 
   parser_name_currencies e ON e.id = c.id_name_currency
   WHERE c.date = CURRENT_DATE() 
   AND c.id_name_banks != 233 
   AND value_sale != 0 
GROUP BY NCValue, NCValue_sale,c.id_name_banks;

He gives me all the banks, i.e. sorting no longer works.
The logic is as follows:
We take the value_buy value, look for it in the parser_all_exchange_rates table and get id_name_banks and place the result in the id_name_banks_buy column. All here is the one I have a plug.
Here is the last version of my progress, where what am I doing wrong?

SELECT
   e.name_currency_en AS NCValue,
   e.name_currency_en AS NCValue_sale,
   MAX(c.value) AS value_buy, 
   MIN(c.value_sale) AS value_sale, 
   c.id_name_banks AS id_name_banks_buy
FROM 
   parser_all_exchange_rates c
INNER JOIN 
   parser_name_currencies e ON e.id = c.id_name_currency
WHERE 
   c.date = CURRENT_DATE() 
   AND c.id_name_banks != 233 
   AND value_sale != 0 
   AND c.id_name_banks = 
   (
      SELECT 
         c.id_name_banks
      FROM
         parser_all_exchange_rates c
      WHERE 
         c.value_sale ORDER BY value_sale ASC LIMIT 1
   )
GROUP BY NCValue, NCValue_sale, c.id_name_banks;

Answer 1

SELECT 
  val.*,
  val_sale.sale,
  ANY_VALUE(bi.id_name_banks) buy_bank,
  ANY_VALUE(si.id_name_banks) sale_bank,
  val_cbrf.value
FROM

(
   SELECT MAX(value) 
      buy, 
      id_name_currency 
   FROM 
      parser_all_exchange_rates
   WHERE 
      date = CURRENT_DATE() AND
      id_name_banks != 233
   GROUP BY 
      id_name_currency
) val,

(
   SELECT 
      MIN(value_sale) sale, 
      id_name_currency
   FROM 
      parser_all_exchange_rates
   WHERE 
      date = CURRENT_DATE() AND
      value_sale > 0 AND
      id_name_banks != 233
   GROUP BY 
      id_name_currency
) val_sale,

(
   SELECT 
      r.value,
      r.id_name_currency
   FROM 
      parser_all_exchange_rates r
   WHERE
      r.id_name_banks = 233 AND 
      r.date = CURRENT_DATE()
) val_cbrf,
   
   parser_all_exchange_rates bi,
   parser_all_exchange_rates si
WHERE 
  bi.id_name_currency = val.id_name_currency AND 
  val.buy = bi.value AND 
  val_sale.id_name_currency = val.id_name_currency AND
  bi.date = CURRENT_DATE() AND
  si.id_name_currency = val.id_name_currency AND
  val_sale.sale = si.value_sale AND
  si.date = CURRENT_DATE() AND 
  val_cbrf.id_name_currency = val.id_name_currency
GROUP BY 
   val.id_name_currency, 
   val.buy, 
   val_sale.sale,
   val_cbrf.value