Help with distinct

Question

Help with distinct

Navigation

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

0

I have the following sql query:

select nome, data, situacao from cadastro

The result is like this:

MARIA   01/01/2018  0
MARIA   15/01/2018  1
GISELE  15/01/2018  0
CICERA  08/01/2018  1
ANTONIA 20/01/2018  0
ANTONIA 15/01/2018  1

I need to make a distinct by name:

select distinct(nome), data, situacao from cadastro group by data, situacao

The problem is that it does not make the distinct because the dates are different, however, I need that, when the patient has two tokens (situation 0 and 1), only the listing with situation = 1 is listed. >

In this case, you should generate the result like this:

MARIA   15/01/2018  1
GISELE  15/01/2018  0
CICERA  08/01/2018  1
ANTONIA 15/01/2018  1

Any suggestions?

sql query postgresql select

asked by anonymous 29.01.2018 / 14:12

4 answers

1

I did it in a non-simplified way, but solved your problem, I had to add an ID to get the correct registers in the where if you have another identifier the logic is the same

select c.nome, c.data, c.situacao 
    from cadastro c 
    where c.id in (select max(cs.id)
                    from cadastro cs where cs.situacao = 1
                    group by cs.nome) 
        or (c.situacao = 0 and 
                c.nome not in (select css.nome
                            from cadastro css where css.situacao = 1
                            group by css.nome))

This code is available in SQL snippet

29.01.2018 / 14:41

1

I made an example by grouping only by nome , and using max(situacao) , then I use this table with join in select to fetch the other data. See:

select
    y.nome,
    x.data,
    y.situacao
from tabela x
inner join (
            select
              t.nome,
              max(t.situacao) as situacao
            from tabela t
            group by t.nome) y on y.nome = x.nome and y.situacao = x.situacao

I put it in SQLFiddle: link

Result:

29.01.2018 / 14:41

1

Using the same logic as MAX() and Group By I made another solution alternative with subselect :

SELECT 
  DISTINCT(C1.nome)
  , (
    SELECT c2.data 
    FROM Cadastro AS c2 
    WHERE c2.situacao = MAX(C1.situacao)
    AND c2.Nome = C1.Nome
  )
  , MAX(C1.situacao)
FROM 
  Cadastro AS C1
GROUP BY C1.Nome

Example online: SQLFiddle

29.01.2018 / 15:34

Creating a dynamic array that stores text with breaks Distributing my app via email

score 2 · Accepted Answer

Your distinct will not work because you're adding data to the result.

I suggest you enumerate your lines with the function ROW_NUMBER by targeting by name and sorting the situation from the highest to the lowest .

SELECT l.*
FROM
  (SELECT Cadastro.*,
           ROW_NUMBER() OVER (PARTITION BY nome
                              ORDER BY situacao desc) AS linha
   FROM Cadastro) l
WHERE LINHA = 1;

SqlFiddle

ROW_NUMBER

Define the output of a result of numbers. More specifically, returns the sequence number (1,2,3,4...) of a line within a partition of a set of results, starting at 1 for the first line in each partition.

Responding to your comment:

WHERE LINHA = 1 will be the first sequential line. If you need to change to get the lines with situacao = 0 , just change to WHERE LINHA = 2 .

PARTITION BY value_expression

Divide the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. value_expression Specifies the column by which the result set is partitioned. If PARTITION BY is not specified, the function treats all rows in the set as a single query result set.

Some similar answers:

See more in the documentation:

PostgreSQL ROW_NUMBER function