SQL query Advanced filter with Postgres

Question

SQL query Advanced filter with Postgres

Navigation

#1 by (2 votes)

2

I have this database model

I want to make a query that meets the following conditions.

all students must be listed

I want to be able to see the type of characterization only of the characterization with the highest order semester

students should not repeat

I have already tried in different ways but I always get all the characterizations that the student has and I want to see only the type of characterization with the semester of highest order for each student.

example:

I'm doing this consultation

select e.nombre, c.id, s.nombre as "semestre", s.orden, t.nombre as "tipo" 
from estudiante e, caracterizacion c, tipo t, semestre s 
where e.id=c.estudiante_id and t.id=c.tipo_id and s.id=c.semestre_id

and it is giving me the following results:

but I want only the following results to come out:

Those that are enclosed in red are those that I want to give me as a result and correspond to the semester of higher order of the characterizations of each student.

I hope you can help me, annex code for generating the database:

CREATE TABLE "Estudiantes" (
    "id" serial NOT NULL,
    "nombre" varchar NOT NULL,
    "tipo_documento" varchar NOT NULL,
    "documento" varchar NOT NULL,
    "celular" varchar NOT NULL,
    "telefono" varchar NOT NULL,
    "genero" integer NOT NULL,
    "fecha_nac" varchar NOT NULL,
    "email" varchar NOT NULL,
    "direccion" varchar NOT NULL,
    "foto" BINARY NOT NULL,
    "programa" integer NOT NULL,
    "Estado" integer NOT NULL,
    "Created" VARCHAR(255) NOT NULL,
    CONSTRAINT Estudiantes_pk PRIMARY KEY ("id")
) WITH (
OIDS=FALSE
);



CREATE TABLE "Programas" (
    "id" serial NOT NULL,
    "nombre" varchar NOT NULL,
    "duracion_semestres" integer NOT NULL,
    "snies" varchar NOT NULL,
    "Franja" VARCHAR(255) NOT NULL,
    CONSTRAINT Programas_pk PRIMARY KEY ("id")
) WITH (
OIDS=FALSE
);



CREATE TABLE "Semestres" (
    "id" serial NOT NULL,
    "semestre" varchar NOT NULL,
    "orden" integer NOT NULL,
    CONSTRAINT Semestres_pk PRIMARY KEY ("id")
) WITH (
OIDS=FALSE
);



CREATE TABLE "Caracterizaciones" (
    "id" serial NOT NULL,
    "estudiante" integer NOT NULL,
    "descripcion" varchar NOT NULL,
    "semestre" integer NOT NULL,
    "Nivelado" integer NOT NULL,
    "tipo" integer NOT NULL,
    "usuario" integer NOT NULL,
    "Created " VARCHAR(255) NOT NULL,
    CONSTRAINT Caracterizaciones_pk PRIMARY KEY ("id")
) WITH (
OIDS=FALSE
);



CREATE TABLE "Tipos_caracterizaciones" (
    "id" serial NOT NULL,
    "nombre" serial NOT NULL,
    "default" BOOLEAN NOT NULL,
    CONSTRAINT Tipos_caracterizaciones_pk PRIMARY KEY ("id")
) WITH (
OIDS=FALSE
);



CREATE TABLE "Users" (
    "id" serial NOT NULL,
    "user_name" varchar NOT NULL UNIQUE,
    "nombre" varchar NOT NULL,
    "apellido" VARCHAR(255) NOT NULL,
    "password" varchar NOT NULL,
    "email" varchar NOT NULL,
    "grupo" integer NOT NULL,
    CONSTRAINT Users_pk PRIMARY KEY ("id")
) WITH (
OIDS=FALSE
);



CREATE TABLE "Grupos" (
    "id" serial NOT NULL,
    "nombre" varchar NOT NULL,
    CONSTRAINT Grupos_pk PRIMARY KEY ("id")
) WITH (
OIDS=FALSE
);



CREATE TABLE "Genero" (
    "id" serial NOT NULL,
    "nombre" VARCHAR(255) NOT NULL,
    CONSTRAINT Genero_pk PRIMARY KEY ("id")
) WITH (
OIDS=FALSE
);



ALTER TABLE "Estudiantes" ADD CONSTRAINT "Estudiantes_fk0" FOREIGN KEY ("genero") REFERENCES "Genero"("id");
ALTER TABLE "Estudiantes" ADD CONSTRAINT "Estudiantes_fk1" FOREIGN KEY ("programa") REFERENCES "Programas"("id");



ALTER TABLE "Caracterizaciones" ADD CONSTRAINT "Caracterizaciones_fk0" FOREIGN KEY ("estudiante") REFERENCES "Estudiantes"("id");
ALTER TABLE "Caracterizaciones" ADD CONSTRAINT "Caracterizaciones_fk1" FOREIGN KEY ("semestre") REFERENCES "Semestres"("id");
ALTER TABLE "Caracterizaciones" ADD CONSTRAINT "Caracterizaciones_fk2" FOREIGN KEY ("tipo") REFERENCES "Tipos_caracterizaciones"("id");
ALTER TABLE "Caracterizaciones" ADD CONSTRAINT "Caracterizaciones_fk3" FOREIGN KEY ("usuario") REFERENCES "Users"("id");


ALTER TABLE "Users" ADD CONSTRAINT "Users_fk0" FOREIGN KEY ("grupo") REFERENCES "Grupos"("id");

sql postgresql consulta

asked by Cesar Martinez Quiroga 20.11.2018 в 06:31

source

1 answer

How can I remove a field from a multidimensional array? Insert and Search in Datetime field

score 2 · Accepted Answer

For a point of order I would recommend you use explicit JOIN :

SELECT 
    e.nombre, 
    c.id, 
    s.nombre AS semestre, 
    s.orden, 
    t.nombre AS tipo
FROM estudiante e, 
JOIN caracterizacion c ON c.estudiante_id = e.id 
JOIN tipo t ON t.id = c.tipo_id
JOIN semestre s ON s.id = c.semestre_id

Second, your tables have a plural name and your example in the singular. We are going to ignore this aspect.

As I see it, your problem is that caracterizacion is a bridge table between students and semesters, like

estudiante_id | semestre_id
---------------------------
      1       |     1
      1       |     2
      1       |     3
      2       |     1
      2       |     2
      2       |     3
      3       |     1
      3       |     2
      3       |     3

I think you may have the same problem with the tipo entity that you have not noticed yet. If in every record of caracterización any estudiante can be displayed combined with any semestre and any tipo then the problem will be repeated. However, let's simplify the problem . Let's say that tipo is left out and the problem is reduced to:

SELECT 
    e.nombre, 
    c.id, 
    s.nombre AS semestre, 
    s.orden
FROM estudiante e, 
JOIN caracterizacion c ON c.estudiante_id = e.id 
JOIN semestre s ON s.id = c.semestre_id

I think there are four ways to do what you are looking for.

The horrible way (do not do it please)

You can do that in each row the order is the maximum possible since for that row you know the id of the student:

SELECT
    e.nombre,
    c.id,
    s.nombre AS semestre,
    s.orden
FROM estudiante e
JOIN caracterizacion c ON c.estudiante_id = e.id
JOIN semestre s ON s.id = c.semestre_id
WHERE s.orden = (
    SELECT max(orden) FROM caracterizacion c2
    JOIN semestre s2 ON s2.id = c2.semestre_id
    WHERE c2.estudiante_id=c.estudiante_id
)

In the background you execute a subquery for each row. Inefficient but you get three rows for three students

The traditional way

You can run a sub-query a priori to know in advance the student-semester tuple that fulfills your condition:

WITH maximo_semestre AS (
  SELECT c.estudiante_id,max(s.orden) AS orden
FROM caracterizacion c
JOIN semestre s ON s.id = c.semestre_id
GROUP BY estudiante_id
)
SELECT
    e.nombre,
    c.id,
    s.nombre AS semestre,
    s.orden
FROM estudiante e
JOIN caracterizacion c ON c.estudiante_id = e.id
JOIN semestre s ON s.id=c.semestre_id
JOIN maximo_semestre ms ON ms.estudiante_id=e.id AND ms.orden=s.orden

(the use of WITH is only to not do the JOIN with the subquery that looks more messy). Again, you get three rows.

Using window functions

The use of window functions allows you to know local aggregates on a segment of the query. In this case you will not get the benefit but it fulfills the purpose.

WITH window_query AS (
    SELECT
    e.nombre,
    c.id,
    s.nombre AS semestre,
    s.orden,
    max(s.orden) OVER (partition BY e.nombre) max_orden
FROM estudiante e
JOIN caracterizacion c ON c.estudiante_id = e.id
JOIN semestre s ON s.id=c.semestre_id
)
SELECT nombre,
       id,
       semestre,
       orden 
FROM window_query
WHERE orden = max_orden

The elegant way

You can use DISTINCT ON by combining with ORDER BY to get all the different names that would appear in your first query You'll only get 3 records so you'll have to play with the ORDER BY of the secondary fields to get the rows you want.

SELECT DISTINCT ON (e.nombre)
    e.nombre,
    c.id,
    s.nombre AS semestre,
    s.orden
FROM estudiante e
JOIN caracterizacion c ON c.estudiante_id = e.id
JOIN semestre s ON s.id=c.semestre_id
ORDER BY e.nombre, s.orden DESC