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

i using oracle 11g release 1. following on this questions.

having set of rows:

name     contact_no         123        124 b        125 c        126 c        127 

i return using analytic functions:

name     contact_no b        125 

i

select name, contact_no   tbl_name  name in ( select name                    tbl_name                  group  name                  having count(name) = 1                 ) 

or this

select name, max(contact_no)   tbl_name group  name having count(name) = 1 

but want solution can avoid subquery or joining table or using min/max confuse reads code! believe possible analytic functions don't know how?

you're right, need use analytic functions; if want return entire row. judging current query analytic count().

select *   ( select a.*, count(*) on ( partition name ) ct            tbl_name )  ct = 1 

to explain, partition is, effectively, same group by, except without doing one. counts number of records per name , returns in-line, no matter number of records name. can restrict on generated column.

although use sub-query there's nothing intrinsically wrong method. scan table/index once; unlike in current example scan twice.

i recommend have index on name here, depend on specific circumstances. may need scan full table, in case index not you.

furthermore, if only have these 2 columns there's no point in analytic query. can use second option obtain same result.

select name, max(contact_no)   tbl_name  group name having count(name) = 1 

analytic queries useful when there's additional columns need return, can't include in group by.