How to sort a select by bringing results of 2 in 2

Question

I am creating a system where the user informs the name and time of birth. I would like to know whether the following question can be resolved: I have the following table:

Having the name and time of birth, the system administrator informs the priority of each record, being:

1 = high priority, 2 = high priority, 3 = low priority, 4 = no high priority. Then I want these records to be displayed in priority order, and 2 in 2. Example: 2 priority records 1... then 2 priority records 2... 2 priority records 3...etc No, it needs to be in the same select, it could use more than one select, but as long as there are records, I need to list 2 in 2, without repeating them. Would look like this:

I’m using Mysql and Php. Does anyone have any idea how I can do this? Thank you!

Follow the mysql table:

CREATE TABLE `tbl_nasc_users` (
  `id` int(111) NOT NULL,
  `nome` varchar(30) NOT NULL,
  `hr_nasc` time NOT NULL,
  `prioridade` int(9) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `tbl_nasc_users` (`id`, `nome`, `hr_nasc`, `prioridade`) VALUES
(1, 'Pedro', '17:34:40', 1),
(2, 'João', '17:23:18', 2),
(3, 'Marcos', '17:56:39', 2),
(4, 'Vinicius', '18:12:48', 1),
(5, 'Miguel', '18:36:53', 3),
(6, 'Bruno', '18:30:10', 4),
(7, 'Felipe', '18:48:42', 3),
(8, 'Antonio', '19:34:40', 1),
(9, 'Victor', '19:23:18', 2),
(10, 'Ronaldo', '19:56:39', 2),
(11, 'Ricardo', '20:12:48', 1),
(12, 'Teodoro', '20:36:53', 3),
(13, 'Gabriel', '21:30:10', 4),
(14, 'Patrick', '22:48:42', 3);

ALTER TABLE `tbl_nasc_users`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `tbl_nasc_users`
  MODIFY `id` int(111) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

Answer 1

Since it is the administrator who informs the priority, I thought it could 'group' by schedule. The priority field has been divided into two, there is a priority separated by 'first hour', 'second hour', etc. A precedent to prioritize these staff could easily be created.

The table would look like this:

CREATE TABLE tbl_nasc_users (  id int NOT NULL,  nome varchar(30) NOT NULL, hr_nasc time NOT NULL,  prioridade int NOT NULL, horario int not null ) 

And the administrator could classify them like this:

INSERT INTO tbl_nasc_users (id, nome, hr_nasc, prioridade,horario) VALUES    (1, 'Pedro', '17:34:40', 1, 1), (2, 'João', '17:23:18', 2, 1), (3, 'Marcos', '17:56:39', 2, 1), (4, 'Vinicius', '18:12:48', 1,1), 
 (5, 'Miguel', '18:36:53', 3,2), (6, 'Bruno', '18:30:10', 4,2), (7, 'Felipe', '18:48:42', 3,2), (8, 'Antonio', '19:34:40', 1,3), (9, 'Victor', '19:23:18', 2,3), 
 (10, 'Ronaldo', '19:56:39', 2,4), (11, 'Ricardo', '20:12:48', 1,4), (12, 'Teodoro', '20:36:53', 3,4), (13, 'Gabriel', '21:30:10', 4,4), (14, 'Patrick', '22:48:42', 3,4);

Then the return would be simple:

select * from tbl_nasc_users order by horario, prioridade

Answer 2

You can add up the amount of records and use this in a mathematical formula. The only problem with this is that you are limited to the four priorities you have set, but if they are fixed in your bank I see no problems.

This consultation should bring the expected result:

SET @qtd_1 = 0;
SET @qtd_2 = 0;
SET @qtd_3 = 0;
SET @qtd_4 = 0;

SET @aux_1 = false;
SET @aux_2 = false;
SET @aux_3 = false;
SET @aux_4 = false;

SELECT
nome, prioridade,
(CASE prioridade
  WHEN 1 THEN
    ((@qtd_1 := @qtd_1 + IF(@aux_1 := !@aux_1, 1, 0)) * 100) + prioridade
  WHEN 2 THEN
    ((@qtd_2 := @qtd_2 + IF(@aux_2 := !@aux_2, 1, 0)) * 100) + prioridade
  WHEN 3 THEN
    ((@qtd_3 := @qtd_3 + IF(@aux_3 := !@aux_3, 1, 0)) * 100) + prioridade
  WHEN 4 THEN
    ((@qtd_4 := @qtd_4 + IF(@aux_4 := !@aux_4, 1, 0)) * 100) + prioridade
  ELSE 0 END) AS prioridade_real
FROM minha_tabela
ORDER BY prioridade_real;