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