Help with DISTINCT in Oracle

Question

Help with DISTINCT in Oracle

Navigation

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

0

I've been trying to get a SELECT out of the job since yesterday. Let's see if you are able to help me.

Tables:

directions

ID             NUMBER
TIPO_Direccion NUMBER
id_persona     NUMBER
direccion      VARCHAR2(50)

people

id     NUMBER
nombre VARCHAR2(50)

The fact is that I relate the tables like this SELECT per.id AS id_person , per.name , dir.id AS address_id , dir.type_adirection , dir.direccion FROM people per JOIN addresses dir ON per.id = dir.id_person ;

The type of address can be:

- nacional := 0
- principal := 1
- segunda casa := 2

Each person can have only one "national" or "main" and different "second home".

They ask me to appear in that order and only one person. The problem is that I can not make "distinct" work.

Everything must be in a mounted View, I do not have functions although they can be called.

EDIT:

In principle I used the DISTINCT in this way:

SELECT DISTINCT (per.id AS id_persona)
, per.nombre
, dir.id AS id_direccion
, dir.tipo_direccion
, dir.direccion
FROM personas per
JOIN direcciones dir ON per.id = dir.id_persona
ORDER BY dir.tipo_direccion
;

But all the possible directions of the person appear to me. It is a kind of prioritization by the type_address field: First, those with the "national" type of address: = 0 Second, those who have it "principal": = 1 Third, those who have it in "second house": = 2

And that only one address appears per person. If I have Bill Gates that only appears his address in California since it is the type that has a national address. And do not show me more directions.

oracle

asked by bitelmaniaco 30.08.2017 в 13:17

source

2 answers

Run two methods but wait for only one CSS Laravel Bootstrap 4

score 2 · Answer 1

You can use ROW_NUMBER :

SELECT  id_persona, 
        per.nombre, 
        id_direccion, 
        dir.tipo_direccion, 
        dir.direccion
FROM (  SELECT  per.id AS id_persona, 
                per.nombre, 
                dir.id AS id_direccion, 
                dir.tipo_direccion, 
                dir.direccion,
                ROW_NUMBER() OVER(PARTITION BY per.id ORDER BY dir.tipo_direccion)  AS rn
        FROM personas per
        INNER JOIN direcciones dir 
            ON per.id = dir.id_persona) AS t
WHERE t.rn = 1
;

score 0 · Answer 2

The following query gives you the complete result by making a DISTINCT on each person.

And by ORDER BY you will be sorted by the field you want. Sorted ASC (ascending) will appear first 0 (national) and at the end 2 (second house).

PARTITION BY serves to indicate that you only want 1 record for each person in this case.

SELECT 
DISTINCT DIRECCIONES.ID_PERSONA,
DIRECCIONES.ID,
DIRECCIONES.TIPO_DIRECCION OVER (PARTITION BY DIRECCIONES.ID_PERSONA),
DIRECCIONES.DIRECCION
FROM
DIRECCIONES,
PERSONAS
WHERE
DIRECCIONES.ID_PERSONA = PERSONAS.ID
ORDER BY DIRECCIONES.TIPO_DIRECCION ASC