Sort alphabetically two columns, PostgreSQL

Question

Sort alphabetically two columns, PostgreSQL

Navigation

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

0

I have the following query:

 SELECT carreras.nombre,grupos_acta.grado,count(grupos_acta.grado) AS grupos ,sum(hombres),sum(mujeres),descripcion 
 FROM control.asignacion_grupos
 INNER JOIN control.grupos_acta ON grupos_acta.id = id_grupos_acta
 INNER JOIN control.carreras ON carreras.id = id_carreras
 INNER JOIN control.estadistica ON estadistica.id_asignacion_grupos = asignacion_grupos.id
 INNER JOIN control.modalidad ON modalidad.id = id_modalidad
 WHERE id_ciclos = 4 AND grupos_acta.id_campus = 1 AND carreras.tipo=1
 GROUP BY descripcion,carreras.nombre,grupos_acta.grado
 ORDER BY nombre ASC

which gives me the result:

The query gives me the data I ask, the detail I have is that I need to alphabetically order two columns which are name and description , and tried to do ORDER BY nombre,descripcion ASC , but I can not get results, I would like to know what would be the correct way to sort two columns alphabetically, ie, that Bachelor of Business Administration and your description I started in school, I hope to make myself understood, thank you.

UPDATE In the same way and the other type, but it does not give me the results I want.

postgresql

asked by Miguel Osorio 29.12.2016 в 15:46

source

3 answers

Recursive MySQL query to parents and siblings Django: show all the variables in a template

score 2 · Answer 1

Solution:


SELECT carreras.nombre,grupos_acta.grado,count(grupos_acta.grado) AS grupos ,sum(hombres),sum(mujeres),descripcion 
 FROM control.asignacion_grupos
 INNER JOIN control.grupos_acta ON grupos_acta.id = id_grupos_acta
 INNER JOIN control.carreras ON carreras.id = id_carreras
 INNER JOIN control.estadistica ON estadistica.id_asignacion_grupos = asignacion_grupos.id
 INNER JOIN control.modalidad ON modalidad.id = id_modalidad
 WHERE id_ciclos = 4 AND grupos_acta.id_campus = 1 AND carreras.tipo=1
 GROUP BY descripcion,carreras.nombre,grupos_acta.grado
 ORDER BY carreras.nombre,descripcion ASC

I see that in your query you do not have both columns in ORDER BY

Update 1: You say:

Bachelor of Business Administration and its description starts in school

I see you have

Domingos
Escolarizados
Sabados

Your alphabetical order is such that PostgreSql is ordering it so that you indicate ASC or DESC and do it correctly. I think you have a confusion in alphabetical order, because it is:

A, B, C, D (Sundays), E (Scholarized), F, G, H, I, J, K, L, M, N, O, P, Q, R, S (Saturdays), T, U ,V W X Y Z

score 0 · Answer 2

I guess that in the table modalidad is where the field descripcion is, I see that the query is sorted first by name and then by description and the result corresponds to it. However, you could have better order control by adding a whole field posicion in the table of modalidad , this way you could have

 ORDER BY nombre, modalidad.posicion

score 0 · Answer 3

The problem is not the ORDER BY, it should work simply by: ORDER BY nombre,descripcion ASC , the next fiddle (simplified) proves it.

If it does not work for you, there should be a problem in the JOIN or in the GROUP BY or elsewhere.

SQL Fiddle

PostgreSQL 9.3 Schema Setup :

create table carrera (
    id serial primary key,
    nombre character varying (50),
    descripcion character varying (20)
);

insert into carrera (nombre,descripcion) values 
('Licenciatura Adm. de Empresas','Domingos'),
('Licenciatura Adm. de Empresas','Sábados'),
('Licenciatura Adm. de Empresas','Domingos'),
('Licenciatura Adm. de Empresas','Escolarizado'),
('Licenciatura Adm. de Empresas','Domingos'),
('Licenciatura Adm. de Empresas','Domingos'),
('Licenciatura Adm. de Empresas','Sábados'),
('Licenciatura Adm. de Empresas','Domingos'),
('Licenciatura Adm. de Empresas','Escolarizado'),
('Licenciatura Adm. de Empresas','zzz'),
('aaaa','bbb'),
('aaaa','ccc'),
('aaaa','ddd'),
('zzzz','aaaa'),
('zzzz','bb'),
('zzzz','c'),
('zzzz','d'),
('zzzz','e')
;

Query 1 :

SELECT
  *
FROM 
  carrera
ORDER BY nombre,descripcion ASC

Results :

| id |                        nombre |  descripcion |
|----|-------------------------------|--------------|
| 11 |                          aaaa |          bbb |
| 12 |                          aaaa |          ccc |
| 13 |                          aaaa |          ddd |
|  5 | Licenciatura Adm. de Empresas |     Domingos |
|  1 | Licenciatura Adm. de Empresas |     Domingos |
|  6 | Licenciatura Adm. de Empresas |     Domingos |
|  3 | Licenciatura Adm. de Empresas |     Domingos |
|  8 | Licenciatura Adm. de Empresas |     Domingos |
|  4 | Licenciatura Adm. de Empresas | Escolarizado |
|  9 | Licenciatura Adm. de Empresas | Escolarizado |
|  7 | Licenciatura Adm. de Empresas |      Sábados |
|  2 | Licenciatura Adm. de Empresas |      Sábados |
| 10 | Licenciatura Adm. de Empresas |          zzz |
| 14 |                          zzzz |         aaaa |
| 15 |                          zzzz |           bb |
| 16 |                          zzzz |            c |
| 17 |                          zzzz |            d |
| 18 |                          zzzz |            e |