Oracle - Select Rows Where Column Value Appears Once Only

In Oracle, suppose there is a table having 3-4 or more columns in it and you want to select rows where a column value appears once only. Means a column value exists in one record only not in any other row and you want to select all the columns of that table. Below is an example of this:

Oracle SQL Query to Select Rows Where Column Value Appears Once Only

Here is the dept table data:

DEPTNO	DNAME	        LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	NEW YORK
30	SALES	        NEW YORK
40	OPERATIONS	BOSTON
99	testdept	NEW JERSEY
55	FINANCE	        NEW JERSEY
56	HARDWARE	HOUSTON

You require to select rows where LOC column value appears once only, like BOSTON and HOUSTON in the above data set. To achieve this, I am using COUNT() OVER function. Below is an example:

SELECT deptno, dname, loc
  FROM (SELECT d.*, COUNT (loc) OVER (PARTITION BY loc ORDER BY loc) cnt
          FROM dept d)
 WHERE cnt = 1;

Output

DEPTNO	DNAME	        LOC
40	OPERATIONS	BOSTON
56	HARDWARE	HOUSTON

To understand this, if you will run the sub-query from the above query you will get the following result:

Query and Its Output

SELECT d.*, COUNT (loc) OVER (PARTITION BY loc ORDER BY loc) cnt
  FROM dept d;

DEPTNO	DNAME	        LOC	        CNT
40	OPERATIONS	BOSTON	        1
56	HARDWARE	HOUSTON	        1
99	testdept	NEW JERSEY	2
55	FINANCE	        NEW JERSEY	2
20	RESEARCH	NEW YORK	3
30	SALES	        NEW YORK	3
10	ACCOUNTING	NEW YORK	3

See also:

SQL*PLUS Script Examples