Select distinct and non-distinct column values

Question

I want to run a query that eliminates duplicate rows based on the values of colA and colB. However, I still want to return the values of colC and colD.

Here is a simple example of what I have so far:

SELECT DISTINCT colA, colB   ?colC, colD?
FROM TABLE1

or

SELECT colA, colB    ?colC, colD?
FROM TABLE1
GROUP BY colA, colB

Either method does not allow me to return colC, and colD unless I check them as distinct values or group like colA and colB. I do not want to do this, only colA and colB together need to be distinct not colC and colD. I just want to return them.

Any ideas on how I can accomplish this?

Answer 1

Do you want list_agg?

select colA, colB,
       list_agg(distinct colC, ','),
       list_agg(distinct colD, ',')
from Table1
Group by ColA, ColB

If any arbitrary value would do for ColC and colD, you can use min():

select colA, colB, min(colC), min(colD)
from Table1
Group by ColA, ColB

Answer 2

The DISTINCT applies to all values you are selecting, not just to some columns. In your case it will apply to all: colA, colB, colC, colD. It is impossible to select all columns and make some distinct and some not. The only way to do this shown in Gordon's example, this is the only valid example and answer.