Rosangela Westphal
-
Total de itens
2 -
Registro em
-
Última visita
Posts postados por Rosangela Westphal
-
-
Boa noite pessoa, estou com o seguinte problema: tenha minha tabela conta_a e a mesma é formada geralmente assim 3.1.01 as vezes pode ser inserido 3.1.10, não estou conseguindo ordenar abaixo tenho todos os códigos como quero que fique.
preciso ordenar assim | consulta ordenando codigo como varchar | consulta ordenando replace(codigo,'.','')::int8 |-----------------------+----------------------------------------+---------------------------------------------------+'3.1' | '3.1' | '3.1''3.1.01' | '3.1.01' | '3.2''3.1.02' | '3.1.02' | '3.3''3.1.03' | '3.1.03' | '3.4''3.1.04' | '3.1.04' | '3.5''3.1.05' | '3.1.05' | '3.6''3.1.06' | '3.1.06' | '3.7''3.1.07' | '3.1.07' | '3.8''3.1.08' | '3.1.08' | '3.10''3.1.09' | '3.1.09' | '3.11''3.1.10' | '3.1.10' | '3.12''3.1.11' | '3.1.11' | '3.13''3.1.12' | '3.1.12' | '3.6.1''3.1.13' | '3.1.13' | '3.6.2''3.1.14' | '3.1.14' | '3.7.1''3.1.15' | '3.1.15' | '3.7.2''3.1.16' | '3.1.16' | '3.7.3''3.1.17' | '3.1.17' | '3.7.4''3.1.18' | '3.1.18' | '3.7.5''3.1.19' | '3.1.19' | '3.7.6''3.1.20' | '3.1.20' | '3.7.7''3.1.21' | '3.1.21' | '3.7.8''3.1.22' | '3.1.22' | '3.1.01''3.1.23' | '3.1.23' | '3.10.1''3.1.24' | '3.1.24' | '3.1.02''3.2' | '3.10' | '3.10.2''3.2.01' | '3.10.1' | '3.1.03''3.2.02' | '3.10.2' | '3.1.04''3.2.03' | '3.11' | '3.1.05''3.2.04' | '3.11.1' | '3.1.06''3.2.05' | '3.11.2' | '3.1.07''3.2.06' | '3.11.3' | '3.1.08''3.2.07' | '3.12' | '3.1.09''3.2.08' | '3.12.1' | '3.1.10''3.2.09' | '3.12.2' | '3.1.11''3.2.10' | '3.12.3' | '3.11.1''3.2.11' | '3.12.4' | '3.11.2''3.2.12' | '3.13' | '3.1.12''3.2.13' | '3.13.1' | '3.1.13''3.2.14' | '3.13.10' | '3.11.3''3.2.15' | '3.13.11' | '3.1.14''3.2.16' | '3.13.12' | '3.1.15''3.2.17' | '3.13.2' | '3.1.16''3.2.18' | '3.13.3' | '3.1.17''3.2.19' | '3.13.4' | '3.1.18''3.2.20' | '3.13.5' | '3.1.19''3.2.21' | '3.13.6' | '3.1.20''3.2.22' | '3.13.7' | '3.12.1''3.2.23' | '3.13.8' | '3.1.21''3.2.24' | '3.13.9' | '3.12.2''3.2.25' | '3.2' | '3.1.22''3.2.26' | '3.2.01' | '3.1.23''3.2.27' | '3.2.02' | '3.12.3''3.2.28' | '3.2.03' | '3.12.4''3.2.29' | '3.2.04' | '3.1.24''3.2.30' | '3.2.05' | '3.13.1''3.2.31' | '3.2.06' | '3.13.2''3.2.32' | '3.2.07' | '3.13.3''3.2.33' | '3.2.08' | '3.13.4''3.2.34' | '3.2.09' | '3.13.5''3.2.35' | '3.2.10' | '3.13.6''3.2.36' | '3.2.11' | '3.13.7''3.2.37' | '3.2.12' | '3.13.8''3.2.38' | '3.2.13' | '3.13.9''3.3' | '3.2.14' | '3.2.01''3.3.01' | '3.2.15' | '3.2.02''3.3.02' | '3.2.16' | '3.2.03''3.3.03' | '3.2.17' | '3.2.04''3.3.04' | '3.2.18' | '3.2.05''3.3.05' | '3.2.19' | '3.2.06''3.3.06' | '3.2.20' | '3.2.07''3.3.07' | '3.2.21' | '3.2.08''3.3.08' | '3.2.22' | '3.2.09''3.3.10' | '3.2.23' | '3.2.10''3.4' | '3.2.24' | '3.2.11''3.4.01' | '3.2.25' | '3.2.12''3.4.02' | '3.2.26' | '3.2.13''3.4.03' | '3.2.27' | '3.2.14''3.4.04' | '3.2.28' | '3.2.15''3.4.05' | '3.2.29' | '3.2.16''3.4.06' | '3.2.30' | '3.2.17''3.4.07' | '3.2.31' | '3.2.18''3.4.08' | '3.2.32' | '3.2.19''3.4.09' | '3.2.33' | '3.2.20''3.4.10' | '3.2.34' | '3.2.21''3.4.11' | '3.2.35' | '3.2.22''3.4.12' | '3.2.36' | '3.2.23''3.4.13' | '3.2.37' | '3.2.24''3.4.14' | '3.2.38' | '3.2.25''3.5' | '3.3' | '3.2.26''3.5.01' | '3.3.01' | '3.2.27''3.5.02' | '3.3.02' | '3.2.28''3.5.03' | '3.3.03' | '3.2.29''3.5.04' | '3.3.04' | '3.2.30''3.5.05' | '3.3.05' | '3.2.31''3.5.06' | '3.3.06' | '3.2.32''3.5.07' | '3.3.07' | '3.2.33''3.5.08' | '3.3.08' | '3.2.34''3.5.09' | '3.3.10' | '3.2.35''3.5.10' | '3.4' | '3.2.36''3.5.11' | '3.4.01' | '3.2.37''3.5.12' | '3.4.02' | '3.2.38''3.5.13' | '3.4.03' | '3.3.01''3.6' | '3.4.04' | '3.3.02''3.6.1 ' | '3.4.05' | '3.3.03''3.6.2 ' | '3.4.06' | '3.3.04''3.7' | '3.4.07' | '3.3.05''3.7.1 ' | '3.4.08' | '3.3.06''3.7.2 ' | '3.4.09' | '3.3.07''3.7.3 ' | '3.4.10' | '3.3.08''3.7.4 ' | '3.4.11' | '3.3.10''3.7.5 ' | '3.4.12' | '3.4.01''3.7.6 ' | '3.4.13' | '3.4.02''3.7.7 ' | '3.4.14' | '3.4.03''3.7.8 ' | '3.5' | '3.4.04''3.8' | '3.5.01' | '3.4.05''3.8.01' | '3.5.02' | '3.4.06''3.8.02' | '3.5.03' | '3.4.07''3.8.03' | '3.5.04' | '3.4.08''3.8.04' | '3.5.05' | '3.4.09''3.8.05' | '3.5.06' | '3.4.10''3.8.06' | '3.5.07' | '3.4.11''3.8.07' | '3.5.08' | '3.4.12''3.8.08' | '3.5.09' | '3.4.13''3.8.09' | '3.5.10' | '3.4.14''3.8.10' | '3.5.11' | '3.5.01''3.8.11' | '3.5.12' | '3.5.02''3.8.12' | '3.5.13' | '3.5.03''3.8.13' | '3.6' | '3.5.04''3.8.14' | '3.6.1' | '3.5.05''3.8.15' | '3.6.2' | '3.5.06''3.8.16' | '3.7' | '3.5.07''3.8.17' | '3.7.1' | '3.5.08''3.8.18' | '3.7.2' | '3.5.09''3.8.19' | '3.7.3' | '3.5.10''3.8.20' | '3.7.4' | '3.5.11''3.8.21' | '3.7.5' | '3.5.12''3.8.22' | '3.7.6' | '3.5.13''3.8.23' | '3.7.7' | '3.8.01''3.8.24' | '3.7.8' | '3.8.02''3.8.25' | '3.8' | '3.8.03''3.8.26' | '3.8.01' | '3.8.04''3.8.27' | '3.8.02' | '3.8.05''3.8.28' | '3.8.03' | '3.8.06''3.8.29' | '3.8.04' | '3.8.07''3.8.30' | '3.8.05' | '3.8.08''3.8.31' | '3.8.06' | '3.8.09''3.8.32' | '3.8.07' | '3.8.10''3.8.33' | '3.8.08' | '3.8.11''3.8.34' | '3.8.09' | '3.8.12''3.8.35' | '3.8.10' | '3.8.13''3.8.36' | '3.8.11' | '3.8.14''3.8.37' | '3.8.12' | '3.8.15''3.8.38' | '3.8.13' | '3.8.16''3.8.39' | '3.8.14' | '3.8.17''3.10' | '3.8.15' | '3.8.18''3.10.1' | '3.8.16' | '3.8.19''3.10.2' | '3.8.17' | '3.8.20''3.11' | '3.8.18' | '3.8.21''3.11.1' | '3.8.19' | '3.8.22''3.11.2' | '3.8.20' | '3.8.23''3.11.3' | '3.8.21' | '3.8.24''3.12' | '3.8.22' | '3.8.25''3.12.1' | '3.8.23' | '3.8.26''3.12.2' | '3.8.24' | '3.8.27''3.12.3' | '3.8.25' | '3.8.28''3.12.4' | '3.8.26' | '3.8.29''3.13' | '3.8.27' | '3.8.30''3.13.1' | '3.8.28' | '3.8.31''3.13.2' | '3.8.29' | '3.8.32''3.13.3' | '3.8.30' | '3.8.33''3.13.4' | '3.8.31' | '3.8.34''3.13.5' | '3.8.32' | '3.8.35''3.13.6' | '3.8.33' | '3.8.36''3.13.7' | '3.8.34' | '3.8.37''3.13.8' | '3.8.35' | '3.8.38''3.13.9' | '3.8.36' | '3.8.39''3.13.10' | '3.8.37' | '3.13.10''3.13.11' | '3.8.38' | '3.13.11''3.13.12' | '3.8.39' | '3.13.12'CREATE TABLE conta_a (codigo varchar)
insert into conta_a (codigo) values ('3.1');insert into conta_a (codigo) values ('3.1.01');insert into conta_a (codigo) values ('3.1.02');insert into conta_a (codigo) values ('3.1.03');insert into conta_a (codigo) values ('3.1.04');insert into conta_a (codigo) values ('3.1.05');insert into conta_a (codigo) values ('3.1.06');insert into conta_a (codigo) values ('3.1.07');insert into conta_a (codigo) values ('3.1.08');insert into conta_a (codigo) values ('3.1.09');insert into conta_a (codigo) values ('3.1.10');insert into conta_a (codigo) values ('3.1.11');insert into conta_a (codigo) values ('3.1.12');insert into conta_a (codigo) values ('3.1.13');insert into conta_a (codigo) values ('3.1.14');insert into conta_a (codigo) values ('3.1.15');insert into conta_a (codigo) values ('3.1.16');insert into conta_a (codigo) values ('3.1.17');insert into conta_a (codigo) values ('3.1.18');insert into conta_a (codigo) values ('3.1.19');insert into conta_a (codigo) values ('3.1.20');insert into conta_a (codigo) values ('3.1.21');insert into conta_a (codigo) values ('3.1.22');insert into conta_a (codigo) values ('3.1.23');insert into conta_a (codigo) values ('3.1.24');insert into conta_a (codigo) values ('3.2');insert into conta_a (codigo) values ('3.2.01');insert into conta_a (codigo) values ('3.2.02');insert into conta_a (codigo) values ('3.2.03');insert into conta_a (codigo) values ('3.2.04');insert into conta_a (codigo) values ('3.2.05');insert into conta_a (codigo) values ('3.2.06');insert into conta_a (codigo) values ('3.2.07');insert into conta_a (codigo) values ('3.2.08');insert into conta_a (codigo) values ('3.2.09');insert into conta_a (codigo) values ('3.2.10');insert into conta_a (codigo) values ('3.2.11');insert into conta_a (codigo) values ('3.2.12');insert into conta_a (codigo) values ('3.2.13');insert into conta_a (codigo) values ('3.2.14');insert into conta_a (codigo) values ('3.2.15');insert into conta_a (codigo) values ('3.2.16');insert into conta_a (codigo) values ('3.2.17');insert into conta_a (codigo) values ('3.2.18');insert into conta_a (codigo) values ('3.2.19');insert into conta_a (codigo) values ('3.2.20');insert into conta_a (codigo) values ('3.2.21');insert into conta_a (codigo) values ('3.2.22');insert into conta_a (codigo) values ('3.2.23');insert into conta_a (codigo) values ('3.2.24');insert into conta_a (codigo) values ('3.2.25');insert into conta_a (codigo) values ('3.2.26');insert into conta_a (codigo) values ('3.2.27');insert into conta_a (codigo) values ('3.2.28');insert into conta_a (codigo) values ('3.2.29');insert into conta_a (codigo) values ('3.2.30');insert into conta_a (codigo) values ('3.2.31');insert into conta_a (codigo) values ('3.2.32');insert into conta_a (codigo) values ('3.2.33');insert into conta_a (codigo) values ('3.2.34');insert into conta_a (codigo) values ('3.2.35');insert into conta_a (codigo) values ('3.2.36');insert into conta_a (codigo) values ('3.2.37');insert into conta_a (codigo) values ('3.2.38');insert into conta_a (codigo) values ('3.3');insert into conta_a (codigo) values ('3.3.01');insert into conta_a (codigo) values ('3.3.02');insert into conta_a (codigo) values ('3.3.03');insert into conta_a (codigo) values ('3.3.04');insert into conta_a (codigo) values ('3.3.05');insert into conta_a (codigo) values ('3.3.06');insert into conta_a (codigo) values ('3.3.07');insert into conta_a (codigo) values ('3.3.08');insert into conta_a (codigo) values ('3.3.10');insert into conta_a (codigo) values ('3.4');insert into conta_a (codigo) values ('3.4.01');insert into conta_a (codigo) values ('3.4.02');insert into conta_a (codigo) values ('3.4.03');insert into conta_a (codigo) values ('3.4.04');insert into conta_a (codigo) values ('3.4.05');insert into conta_a (codigo) values ('3.4.06');insert into conta_a (codigo) values ('3.4.07');insert into conta_a (codigo) values ('3.4.08');insert into conta_a (codigo) values ('3.4.09');insert into conta_a (codigo) values ('3.4.10');insert into conta_a (codigo) values ('3.4.11');insert into conta_a (codigo) values ('3.4.12');insert into conta_a (codigo) values ('3.4.13');insert into conta_a (codigo) values ('3.4.14');insert into conta_a (codigo) values ('3.5');insert into conta_a (codigo) values ('3.5.01');insert into conta_a (codigo) values ('3.5.02');insert into conta_a (codigo) values ('3.5.03');insert into conta_a (codigo) values ('3.5.04');insert into conta_a (codigo) values ('3.5.05');insert into conta_a (codigo) values ('3.5.06');insert into conta_a (codigo) values ('3.5.07');insert into conta_a (codigo) values ('3.5.08');insert into conta_a (codigo) values ('3.5.09');insert into conta_a (codigo) values ('3.5.10');insert into conta_a (codigo) values ('3.5.11');insert into conta_a (codigo) values ('3.5.12');insert into conta_a (codigo) values ('3.5.13');insert into conta_a (codigo) values ('3.6');insert into conta_a (codigo) values ('3.6.1');insert into conta_a (codigo) values ('3.6.2');insert into conta_a (codigo) values ('3.7');insert into conta_a (codigo) values ('3.7.1');insert into conta_a (codigo) values ('3.7.2');insert into conta_a (codigo) values ('3.7.3');insert into conta_a (codigo) values ('3.7.4');insert into conta_a (codigo) values ('3.7.5');insert into conta_a (codigo) values ('3.7.6');insert into conta_a (codigo) values ('3.7.7');insert into conta_a (codigo) values ('3.7.8');insert into conta_a (codigo) values ('3.8');insert into conta_a (codigo) values ('3.8.01');insert into conta_a (codigo) values ('3.8.02');insert into conta_a (codigo) values ('3.8.03');insert into conta_a (codigo) values ('3.8.04');insert into conta_a (codigo) values ('3.8.05');insert into conta_a (codigo) values ('3.8.06');insert into conta_a (codigo) values ('3.8.07');insert into conta_a (codigo) values ('3.8.08');insert into conta_a (codigo) values ('3.8.09');insert into conta_a (codigo) values ('3.8.10');insert into conta_a (codigo) values ('3.8.11');insert into conta_a (codigo) values ('3.8.12');insert into conta_a (codigo) values ('3.8.13');insert into conta_a (codigo) values ('3.8.14');insert into conta_a (codigo) values ('3.8.15');insert into conta_a (codigo) values ('3.8.16');insert into conta_a (codigo) values ('3.8.17');insert into conta_a (codigo) values ('3.8.18');insert into conta_a (codigo) values ('3.8.19');insert into conta_a (codigo) values ('3.8.20');insert into conta_a (codigo) values ('3.8.21');insert into conta_a (codigo) values ('3.8.22');insert into conta_a (codigo) values ('3.8.23');insert into conta_a (codigo) values ('3.8.24');insert into conta_a (codigo) values ('3.8.25');insert into conta_a (codigo) values ('3.8.26');insert into conta_a (codigo) values ('3.8.27');insert into conta_a (codigo) values ('3.8.28');insert into conta_a (codigo) values ('3.8.29');insert into conta_a (codigo) values ('3.8.30');insert into conta_a (codigo) values ('3.8.31');insert into conta_a (codigo) values ('3.8.32');insert into conta_a (codigo) values ('3.8.33');insert into conta_a (codigo) values ('3.8.34');insert into conta_a (codigo) values ('3.8.35');insert into conta_a (codigo) values ('3.8.36');insert into conta_a (codigo) values ('3.8.37');insert into conta_a (codigo) values ('3.8.38');insert into conta_a (codigo) values ('3.8.39');insert into conta_a (codigo) values ('3.10');insert into conta_a (codigo) values ('3.10.1');insert into conta_a (codigo) values ('3.10.2');insert into conta_a (codigo) values ('3.11');insert into conta_a (codigo) values ('3.11.1');insert into conta_a (codigo) values ('3.11.2');insert into conta_a (codigo) values ('3.11.3');insert into conta_a (codigo) values ('3.12');insert into conta_a (codigo) values ('3.12.1');insert into conta_a (codigo) values ('3.12.2');insert into conta_a (codigo) values ('3.12.3');insert into conta_a (codigo) values ('3.12.4');insert into conta_a (codigo) values ('3.13');insert into conta_a (codigo) values ('3.13.1');insert into conta_a (codigo) values ('3.13.2');insert into conta_a (codigo) values ('3.13.3');insert into conta_a (codigo) values ('3.13.4');insert into conta_a (codigo) values ('3.13.5');insert into conta_a (codigo) values ('3.13.6');insert into conta_a (codigo) values ('3.13.7');insert into conta_a (codigo) values ('3.13.8');insert into conta_a (codigo) values ('3.13.9');insert into conta_a (codigo) values ('3.13.10');insert into conta_a (codigo) values ('3.13.11');insert into conta_a (codigo) values ('3.13.12');
Ordenação de uma cadeia de string Postgres
em PostgreSQL
Postado
SELECT my_conta_a.*
FROM (SELECT conta_a.*
, CAST (STRING_TO_ARRAY(conta_a.codigo,'.') AS INT[]) AS my_ordem
FROM conta_a
) my_conta_a
ORDER BY my_conta_a.my_ordem
Funcionou assim.