How to sort a select with 2-in-2 results

Question

How to sort a select with 2-in-2 results

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

I am creating a system where the user informs the name and time of birth. I wonder if you can solve the following question: I have the following table:

Havingthenameandtimeofbirth,thesystemadministratorinformsthepriorityofeachrecord,being:

1=veryhighpriority,2=priority,3=lowpriority,4=non-priority.ThenIwanttheserecordstobedisplayedinorderofpriority,and2in2.Example:2priorityrecords1...then2priorityrecords2...twopriority3...etcNo,itneedstobeinthesameselect,itcouldusemorethanoneselect,butaslongastherearerecords,Ineedtolist2in2withoutrepeatingthem.Itwouldlooklikethis:

I'm using MySql and Php. Anyone have any idea how I can do this? Thanks!

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;

php mysql

asked by anonymous 01.03.2017 / 18:07

2 answers

0

You can go adding 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 that you set, but if they are fixed in your bank, I see no problems.

This query 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;

01.03.2017 / 20:15

problem with multidimensional array does not take value Cryptography SHA512 - XAMARIN

score 2 · Accepted Answer

Since it is the administrator who informs the priority, I thought he could 'group' by schedule. The priority field was split in two, there is a separate priority for 'first hour', 'second hour', etc. A procedure to prioritize these personnel could be easily created.

The table looks 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 admin 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);

So the return would be simple:

select * from tbl_nasc_users order by horario, prioridade