Help with distinct

Question

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 do a distinct by name:

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

The problem is that it does not do the distinct because the dates are different, however, I need that when the patient has two charts (situation 0 and 1), only the chart with situation = 1.

In this case, it 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?

Answer 1

Yes your distinct will not work because you are adding to data in the result.

I suggest you list your lines with the function ROW_NUMBER segmenting by name and sort the situation downwards(from the largest to the smallest).

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 number result. More specifically, returns the sequential number(1,2,3,4...) of a row within a partition of a result set, starting at 1 for the first line in each partition.

Replying to your comment:

WHERE LINHA = 1 will be the first sequential line. In case you need to switch to pick up the lines with situacao = 0, just change to WHERE LINHA = 2.

PARTITION BY value_expression

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

Some similar answers:

• Select first record within a SQL segmentation Server
• SQL SERVER odd line queries

See more in the documentation:

Postgresql ROW_NUMBER Function

Answer 2

I did it in an uncomplicated way, but solve your problem, I had to add an ID to get the correct registors in Where if you have another logica idenficador 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

Answer 3

I made an example by grouping only by nome, and using max(situacao), then 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 in Sqlfiddle: http://sqlfiddle.com/#! 17/84343/7

Upshot:

Answer 4

Using the same logic of 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

Online example: Sqlfiddle