CODEWITHSUNDEEP
sqlpostgresql
Kirill Salykin
Kirill Salykin

Reputation: 711

How can I find records having different value in one field

I have table named entities with columns: id, type, status, fingerprint, uuid. http://sqlfiddle.com/#!15/8d9b3/2

Each entity can have both published and draft versions (they have same uuid). To track changes between published and draft version fingerprint used (md5 of attributes).

Please advice how I can find all changed draft entities, eg entities which have same type and uuid, but have different fingerprint.

For these records, which represent draft and published version of one product

#id  #type     #status      #fprint  #uuid
(3, 'Product', 'draft',     'aaaa', '2e92f72a-c55f-42df-ba7f-afcb131cc6ff'),
(4, 'Product', 'published', 'aaab', '2e92f72a-c55f-42df-ba7f-afcb131cc6ff')

I need to have draft version as result.

Thanks!

UPDATE

This query works

select draft.*
from entities draft
  join entities published on published.type = draft.type 
       and published.uuid = draft.uuid and published.status = 'published'
where draft.status = 'draft' and draft.fingerprint != published.fingerprint

But maybe better one exists?

Upvotes: 0

Views: 50

Answers (2)

Glenn
Glenn

Reputation: 9170

Another possible approach, assuming draft and published are the only types and there won't be multiple for a uuid:

WITH multi_fingerprints AS (
  SELECT uuid
    FROM entities
    GROUP BY uuid
    HAVING COUNT(DISTINCT fingerprint) > 1
)
SELECT e.*
  FROM entities e
  JOIN multi_fingerprints m ON (e.uuid = m.uuid AND e.status = 'draft')

Probably should be indexed on uuid as well.

Upvotes: 0

tata.leona
tata.leona

Reputation: 1058

Try this:

select * from  entities e1, entities e2 where e1.id = e2.id and e1.uuid =e2.uuid and e1.fingerprint <> e2.fingerprint

Upvotes: 1

Related Questions