Give a select to return the exclusive values

Asked

Viewed 520 times

0

I have several repeated values, the value can have status (different or different) or I can have two values with different/different status, and I need to compare them (in case I use a key), that for this I need to return the exclusives that are differentiated and that there can be the same information in the different status. I tried to make that select, but I don’t know what could 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:

KEY | STATUS
ABC123 | DIFFERENTIATED
ABC123 | DIFFERENT
ACB321 | DIFFERENTIATED
ACB321 | DIFFERENT
ACC231 | DIFFERENT -> this data that I would like to return

sql postgresql select

  • From what I understand you just want to bring the values with status = "DIFFERENT" and that have no key in the status "differentiated"?

  • Post a tablet with 2 fields and 5 values to give an idea of what you call different or differentiated and another with the result you expect.

  • I changed the post, I do not know if it was clear. Thank you very much for the attention Apprentice and Reginaldo.

  • And if you had two ACC231 records?

  • It would have to return in the same way, but only if it was in DIFFERENT status. This ACC231 data may be duplicated with the same status.

  • but ACB321 has DIFFERENT status, Voce wants it to return too?

Show 1 more comment

2 answers

1

Follow the script, try to use it like this 

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

Upshot:

DIFFERENT ACC231

0

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

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'
;

The EXCEPT it’s like a UNION on the contrary: instead of concatenating the lines of the second query to the first, he remove the lines of the first query that coincide with some of the second. As well as the UNION, the list of the clause SELECT has to hit both darlings.

Browser other questions tagged sql postgresql select

You are not signed in. Login or sign up in order to post.