CODEWITHSUNDEEP
sqloracle-database
Younes
Younes

Reputation: 3293

Query non repeated values for a column with other columns using analytic functions

I am using Oracle 11g Release 1. Following up on this questions.

Having this set of rows:

Name     Contact_No

A        123
A        124
B        125
C        126
C        127

I would like to return this using analytic functions:

Name     Contact_No
B        125

I could do this 

select name, Contact_No
from   tbl_name
where  name in ( SELECT name
                 FROM   tbl_name
                 GROUP  BY name
                 HAVING COUNT(name) = 1
                )

or this

SELECT name, max(Contact_No)
FROM   tbl_name
GROUP  BY name
HAVING COUNT(name) = 1

But I want a solution where I can avoid the subquery or joining the table with itself or using min/max which could confuse anyone who reads the code! I believe this is possible with analytic functions but I don't know how?

Upvotes: 0

Views: 496

Answers (1)

Ben
Ben

Reputation: 52863

You're right, you need to use analytic functions; if you want to return the entire row. Judging by your current query an analytic COUNT().

select *
  from ( select a.*, count(*) over ( partition by name ) as ct
           from tbl_name )
 where ct = 1

To explain, the partition by is, effectively, the same as a GROUP BY, except without doing one. This counts the number of records per name and returns that in-line, no matter the number of records with that name. You can then restrict on this generated column.

Although this does use a sub-query there's nothing intrinsically wrong with this method. You only scan the table/index once; unlike in your current example where you scan it twice.

I would normally recommend that you have an index on NAME here, but it does depend on your specific circumstances. You may need to scan the full table, in which case an index will not help you.

Furthermore, if you only have these two columns there's no point in an analytic query. You can use your second option to obtain the same result.

SELECT name, max(Contact_No)
  FROM tbl_name
 GROUP BY name
HAVING COUNT(name) = 1

Analytic queries are useful when there's additional columns you need to return, which you can't include in the GROUP BY.

Upvotes: 2

Related Questions