Geek Answers Handbook

Select single and fuzzy column values

I want to run a query that eliminates duplicate rows based on colA and colB values. 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

Any of the methods does not allow me to return colC and colD unless I check them as different values ​​or groups, such as colA and colB. I do not want to do this, only colA and colB should be different together, not colC and colD. I just want to get them back.

Any ideas on how I can do this?

+4
source share
3 answers

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 matters for ColC and colD, you can use min() :

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

DISTINCT applies to all the values ​​you select, not just certain columns. In your case, this applies to everyone: colA, colB, colC, colD. It is not possible to select all columns and make some separate and some not. The only way to do this is shown in the example of Gordon, this is the only valid example and answer.

+1
source

Simple installation operations are possible.

with UNION and UNION ALL (see http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm )

UNION colA and colB will delete doubled entries in these columns, while UNION ALL will store all values ​​from colC and cold

0
source

More articles:


All Articles