4

I want to run query like below

select distinct (columnA, columnB, columnC), columnD from MY_TABLE where columnA IS NOT NULL AND columnB IS NOT NULL AND columnC is NOT NULL;

I only want distinct of columnA, columnB and columnC and NOT ON columnD. But SQL developer is pointing an error right after columnA, How can I fix this?

I tried to fix my query using GROUP BY

select columnA, columnB, columnC, (select count(*) from TABLE2 WHERE table2columnA = myTable.columnA) from MY_TABLE myTable where columnA IS NOT NULL AND columnB IS NOT NULL AND columnC is NOT NULL GROUP BY columnA, columnB, columnC;

Notice that my columnD is actually another select statement? But this is giving me error 

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"

This is not a duplicate of that another question

yalkris
  • 2,596
  • 5
  • 31
  • 51
  • 1
    Thank you Vikrant. I cannot mark has duplicate yet ^.^ – MaxVerro May 07 '15 at 16:39
  • 3
    No. It's not duplicate of that question. I am trying to get distinct of columns (A, B & C) and a non-distinct column D. – yalkris May 07 '15 at 19:22
  • Suppose you have the tuples (1, 2, 3, 4), (1, 2, 3, 4), (1, 2, 3, 5) and (0, 2, 3, 4). What would you expect from your query? – LSerni May 07 '15 at 21:18
  • I suggest you to use `OUTER APPLY` like [this](https://stackoverflow.com/a/64728511/4344976). – Muhammad Musavi Nov 07 '20 at 14:19
  • The problem here is DBMS doesn't know what result to show for the other columns (because there can be multiple candidates). For example, this might fix the issue because it output the first candidate SELECT DISTINCT ON (col1) col2, col3 FROM tableA ORDER BY col1, col2, col3; Please refer [this](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) questions second answer – Wenuka Feb 28 '21 at 13:17

1 Answers1

-2
SELECT DISTINCT a,b,c FROM t

is roughly equivalent to:

SELECT a,b,c FROM t GROUP BY a,b,c,t

It's a good idea to get used to the GROUP BY syntax, as it's more powerful. It's a good idea to get used to the GROUP BY syntax, Please see this post:

Possible duplicate ?

Community
  • 1
  • 1
MaxVerro
  • 96
  • 1
  • 9