CODEWITHSUNDEEP
postgresqlgroup-bysql-order-bydistinctplpgsql
codebot
codebot

Reputation: 697

Select distinct not working in complex pl/pgsql query

I have this query in a pl/pgsql function. I am using PostgreSQL 10. 

FOR firstrecord IN 
EXECUTE format( 

 'SELECT  vans.id as vid, adidas.id as aid, 
 vans.color, adidas.color, vans.type, adidas.type 
 FROM shoes 
 FULL JOIN adidas ON shoes.id = adidas.id 
 FULL JOIN shoes ON shoes.id=vans.id
 WHERE  adidas.code = 607 and vans.code = 304 '  )              

 USING acode , vcode 

END LOOP;

This works, but I would like to enforce a SELECT DISTINCT on vans.id AND adidas.id. This is the closest I got 

FOR firstrecord IN 
EXECUTE format( 

 'SELECT DISTINCT ON  (adidas.id)  vans.id as vid, adidas.id as aid, 
 vans.color, adidas.color, vans.type, adidas.type 
 FROM shoes 
 FULL JOIN adidas ON shoes.id = adidas.id 
 FULL JOIN shoes ON shoes.id=vans.id
 WHERE  adidas.code = 607 and vans.code = 304 
 ORDER BY adidas.id,vans.id'  )              

 USING acode , vcode 

END LOOP;

If I try to do something like SELECT DISTINCT ON (adidas.id, vans.id) the DISTINCT does not work, I get duplicates in result.

If I do SELECT DISTINCT vans.id as vid, adidas.id as aid , still the DISTINCT does not work, I get duplicates in result.

How do I fix this?

Thanks

Upvotes: 0

Views: 314

Answers (1)

George S
George S

Reputation: 2151

As you're seeing now, if you use DISTINCT ON( expression1, expression2) it'll count all the combinations of the two expression as distinct, including when one is NULL as distinct from any non-NULL value. It seems like you want just one expression that takes in to account the ids from both tables. You can get this using the coalesce function, like so:

SELECT DISTINCT ON  ( coalesce(adidas.id, vans.id))  vans.id as vid, adidas.id as aid, 
vans.color, adidas.color, vans.type, adidas.type 
FROM shoes 
FULL JOIN adidas ON shoes.id = adidas.id 
FULL JOIN vans ON shoes.id = vans.id
WHERE  adidas.code = 607 and vans.code = 304 
ORDER BY coalesce(adidas.id, vans.id)

This works in this case because if both are non-NULL, they should match one another, and if one is NULL the coalesce statement will return the non-NULL value.

Upvotes: 1

Related Questions