Give a select to return the unique values

Question

Give a select to return the unique values

Navigation

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

0

I have multiple values repeated, the value can have status (different or different) or I can have two values with different / differentiated statuses, and I need to make a comparison between them (in this case I use a key). unique that are differentiated and that there can not be the same information in different status. I tried to do this select, but I do not know what might be wrong ...

SELECT * from teste WHERE chave IN (select distinct(chave) from teste group by chave having count(chave) > 1) AND chave NOT IN (select distinct(chave) from teste where status = 'diferente')

EXAMPLE:

sql postgresql select

asked by anonymous 12.05.2017 / 21:16

2 answers

create div at runtime Generate random numbers in java, store in a vector and sort them

score 1 · Answer 1

Follow the script, try to use it

 SELECT CHAVE,
        STATUS
   FROM teste
  WHERE status = 'DIFERENTE'
    AND CHAVE NOT IN
        (SELECT CHAVE
         FROM teste
         WHERE STATUS = 'DIFERENCIADO')

Result:

ACC231 DIFFERENT

score 0 · Answer 2

Alternatively, using WITH clauses (some find it easier to reason):

with diferentes as (
    select chave
      from teste
     where status = 'DIFERENTE'
), diferenciados as (
    select chave
      from teste
     where status = 'DIFERENCIADO'
)
select chave
  from diferentes
 where chave not in (
         select chave
           from diferenciados
       )
;

Alternatively, with EXCEPT :

select chave
  from teste
 where status = 'DIFERENTE'
except
select chave
  from teste
 where status = 'DIFERENCIADO'
;

EXCEPT is like UNION in reverse: instead of concatenating the lines of the second query to that of the first, it removes lines from first query that match any of the second. Like UNION , the list of the SELECT clause has to beat both queries .