9

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;
Improve this question

5 Answers 5

Reset to default
7

When you use DISTINCT ON you should have ORDER BY:

SELECT DISTINCT ON (A,B), C INTO tblB FROM tblA ORDER BY A, B;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Nope, I did try that before, that returns only C, like so: tblB: t3a d4g
@NitinGadia . . . This should return unique values for pairs of A and B.
4

This should do the trick

CREATE TABLE tblB AS ( SELECT A, B, max(C) AS max_of_C FROM tblA GROUP BY A, B )
Improve this answer

5 Comments

That worked!! Except one small problem - for some reason column C is now named "max". That's super weird... I can of course rename it, but is there a way to prevent this? Also - what if I want to keep multiple columns, not just C, like what if there was a column D, E, F, that I wanted to keep as well?
I don't think there is a way to prevent this behaviour. If you don't care about the last value, you can use max or min. If you want to keep specific values you may need more complex a query.
That's very strange that it would rename the column. Also, if I needed several columns, you can't keep using "max" and "min" more than once, since you can only specify the column name once. If you have another option that can add multiple columns and retain column names, then can you add that to your answer? That would be awesome :) Thanks for all your help.
You must use aliases, I edited the answer to explain this method.
Unrelated but: the parentheses around the select are not needed (and add useless noise in my opinion).
0

Use a view to do the distinct and then join it to the original table to pick one row of column C. Inserting into the target is left for you to figure out. Oh, and you could pick up multiple columns from t, not just c - the only thing is that your subquery needs to find a way to limit it to only one row.

create table t (a int, b int, c int); create view tv as select distinct a, b from t; insert into t (a, b, c) values(1, 2, 10); insert into t (a, b, c) values(1, 2, 20); insert into t (a, b, c) values(1, 3, 30); insert into t (a, b, c) values(1, 3, 40); CREATE TABLE tblB AS ( select tv.a, tv.b, t.c from tv, t where tv.a = t.a and tv.b = t.b /* pick smallest ctid which is a unique row id built into postgres */ and t.ctid = (select min(ctid) from t s where s.a = t.a and s.b = t.b); )
Improve this answer

4 Comments

that looks way more complicated than the other answers, and also requires you to manually enter a lot of values. The table I'm actually using has thousands of rows.
The other answer is elegant and may suit you. But what are you talking about re entering values??? This is just test code, you would use your own table and only create a view to do the distinct.
oh i see haha yeah, i like the other ones, they are a bit more simple. thanks a lot for your help.
No problem, like I said, they were elegant solutions and I wouldn't have bothered answering if I had seen them first.
0 
drop table t; create table t (a int, b int, c int); insert into t (a, b, c) values(1, 2, 3); insert into t (a, b, c) values(1, 3, 4); insert into t (a, b, c) values(1, 2, 5); insert into t (a, b, c) values(1, 3, 6); select distinct on (a,b) a, b, c from t;

From the docs: "SELECT DISTINCT eliminates duplicate rows from the result. SELECT DISTINCT ON eliminates rows that match on all the specified expressions."

Improve this answer

Comments

0

According to postgresql documentation, you need to specify the names, inside and outside of prantenses.

SELECT DISTINCT ON (A,B) A,B,C INTO tblB FROM tblA;

The image below is another example, with fake data from my project that demonstrates that the intended syntax works properly.

My case

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.