For a table such as this:
tblA
A,B,C
1,2,t3a
1,3,d4g
1,2,b5e
1,3,s6u
I want to produce a table that selects distinct on both A and B simultaneously, and still keep one value of C, like so:
tblB
A,B,C
1,2,t3a
1,3,d4g
Seems like this would be simple, but not finding it for the life of me.
DROP TABLE IF EXISTS tblA CASCADE;
SELECT DISTINCT ON (A,B), C
INTO tblB
FROM tblA;
When you use DISTINCT ON you should have ORDER BY:
SELECT DISTINCT ON (A,B), C
INTO tblB
FROM tblA
ORDER BY A, B;
This should do the trick
CREATE TABLE tblB AS (
SELECT A, B, max(C) AS max_of_C FROM tblA GROUP BY A, B
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With