MySQL: Old ORDER BY in subquery for new DB versions

Question

Hy all, I hope you are well.

I haven't made many questions so I will try to do it the best possible...

I have the following example table:

CREATE TABLE `e_rsts_test` (
  `id` int(11),
  `id_book` int(11),
  `lang` varchar(2),
  `title` varchar(100)
);

INSERT INTO `e_rsts_test` (`id`, `id_book`, `lang`, `title`) VALUES
(33, 51, 'es', 'Tocar los libros'),
(409, 51, 'en', NULL),
(34, 52, 'de', 'Kaput'),
(35, 53, 'fr', 'Chróniques de la dernière Révolution'),
(36, 54, 'es', 'Veneno para la corona'),
(412, 54, 'en', NULL),
(313, 51, 'it', 'Toccare i libri'),
(314, 53, 'en', 'Cronicles of revolution');

that results in the following table:

id |id_book|lang|title
33 |51     |es  |Tocar los libros
409|51     |en  |NULL
34 |52     |de  |Kaput
35 |53     |fr  |Chróniques de la dernière Révolution
36 |54     |es  |Veneno para la corona
412|54     |en  |NULL
313|51     |it  |Toccare i libri
314|53     |en  |Cronicles of revolution

I need to obtain a new table from this, selecting only not NULL titles, and in case there are more than one title, select the record following a priority order by language, thus: if the title in 'es' language exists, that one, else if exists in 'en', that one, else the title in any other language. In the previous example, the table that I need to obtain as a result would be:

id |id_book|lang|title
33 |51     |es  |Tocar los libros
34 |52     |de  |Kaput
314|53     |en  |Cronicles of revolution
36 |54     |es  |Veneno para la corona

I used to solve this problem in my old MySQL database, ordering a sub query in the following way:

SELECT id_book_title, title
FROM (SELECT id_book as id_book_title, title
      FROM e_rsts_test
      ORDER BY ISNULL(title) DESC, (lang = 'es') DESC, (lang = 'en') DESC, id) as c_aux1
GROUP BY id_book_title

But in the new MySQL DB version (10.4.13-MariaDB for the development environment and 8.0.19 for the operative environment), the ORDER BY does not make effect, so I randomly obtain a NULL title in the resulting table.

NOTE: of course, the title languages present in the database can rise up to 100+ an the order criteria depends on the site visitor browser's language configuration. NOTE 2: Each id_book will have at least one not NULL title.

Is there any solution or turnaround to solve this problem in the latests MySQL DB versions? I searched but I could not find anything.

Thanks for your attention and best regards.

Answer 1

On MySQL 8+, we can try using ROW_NUMBER along with FIELD:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id_book
                                 ORDER BY FIELD(lang, 'en', 'es') DESC) rn
    FROM e_rsts_test
    WHERE title IS NOT NULL
)

SELECT id, id_book, lang, title
FROM cte
WHERE rn = 1;

Demo