Mysql - Sort a table and keep records together

Asked

Viewed 133 times

1

I have this table below inside Mysql to which I need to order it from the following form:

  1. The table must be ordered in order of CPF.
  2. But I need the license plates that match, and I need them to hold, dependents stick together keeping the general ordering by CPF as follows. In this case the designation DTOP and for dependents and TOP for holders.

Desired shape:

TOP 101060 97424613170 MARIA FLAVIO 1
DTOP 101060 63455847625 JOAO FLAVIO 0
DTOP 101060 94443050744 BETO FLAVIO 0

BELOW I DISPLAY THE TABLE WITHOUT THE SORT THAT I NEED:

inserir a descrição da imagem aqui

mysql select order

  • Have you tried using GROUP_BY?

  • I tried more then he makes the equal enrollment register group. I need the same license plate records and different names to be separated.

  • Unfortunately Mysql 5.1. And a legacy software that only accepts this version of Mysql

  • SELECT *, (SELECT Cpf FROM sua_table b WHERE b.matricula = a.matricula AND titular = 1) as cpf_tit FROM sua_table a ORDER BY cpf_tit, Cpf;

  • @anonimo Unfortunately it didn’t work. But it gave me some ideas.

  • Just put one ORDER BY matricula, cpf

Show 1 more comment

2 answers

1

Given the following table:

CREATE TABLE `tb_plano_saude` (
`id` int(11) NOT NULL,
`Titular` varchar(10) DEFAULT NULL,
`matricula` varchar(10) DEFAULT NULL,
`CPF` varchar(15) DEFAULT NULL,
`Nome` varchar(45) DEFAULT NULL,
`boleano` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

with the following data:

1   TOP 101060  97424613170 MARIA FLAVIO    1
2   DTOP    101060  63455847625 JOAO FLAVIO 0
3   DTOP    101060  94443050744 BETO FLAVIO 0
4   DTOP    202061  87424613170 MARIA GUSTAVO   0
5   DTOP    202061  43455847625 MARCELO GUSTAVO 0
6   TOP 202061  34443050744 PAULO GUSTAVO   1

the query I made below APPARENTLY returns the result I understood you wanted IMPORTANT.. I assumed that the field "boleano" is also an indicator of ownership

select *  from (
SELECT matricula, cpf,titular,nome, boleano, (matricula + boleano) as b
FROM tb_plano_saude
GROUP BY matricula, cpf
order by matricula, titular desc, cpf)  tb
order by b desc

RESULT:

202061  34443050744 TOP PAULO GUSTAVO   1   202062
202061  43455847625 DTOP    MARCELO GUSTAVO 0   202061
202061  87424613170 DTOP    MARIA GUSTAVO   0   202061
101060  97424613170 TOP MARIA FLAVIO    1   101061
101060  63455847625 DTOP    JOAO FLAVIO 0   101060
101060  94443050744 DTOP    BETO FLAVIO 0   101060

IN CASE the "boleano" field is not a titration indicator, the matricula and the first character of the "titular" field can be concatenated to have the same effect as below

select *  from (
SELECT matricula, cpf,titular,nome, boleano, CONCAT(matricula , 
SUBSTR(titular,1,1)) as b
FROM tb_plano_saude
GROUP BY matricula, cpf
order by matricula, titular desc, cpf)  tb
order by b desc

Getting the result

202061  34443050744 TOP PAULO GUSTAVO   1   202061T
202061  43455847625 DTOP    MARCELO GUSTAVO 0   202061D
202061  87424613170 DTOP    MARIA GUSTAVO   0   202061D
101060  97424613170 TOP MARIA FLAVIO    1   101060T
101060  63455847625 DTOP    JOAO FLAVIO 0   101060D
101060  94443050744 DTOP    BETO FLAVIO 0   101060D

0

Try to assemble an order by as follows:

sua consulta... order by matricula, cpf;

Browser other questions tagged mysql select order

You are not signed in. Login or sign up in order to post.