CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Diana
Diana

Reputation: 33

Query to select data that have only 1 row

i just have the table with data like this

    ID       PHONE_NUMBER          VERSION         VERSION_NAME
   --------------------------------------------------------------
     1       86578900000              0               3.4.2
     2       86578900000              1               3.4.2.1
     3       899589023200             0               3.4.2
     4       878999933336             0               3.4.2
     5       82199987629              0               3.4.2
     6       82199987629              1               3.4.2.1
     7       888729203892             0               3.4.2.1

So, i would like to have the data for phone number that count only have 1 row and version_name is 3.4.2 is like this :

        ID       PHONE_NUMBER        
   -----------------------------
     3           899589023200             
     4           878999933336

I run query 

Select PHONE_NUMBER from TABLE_C where VERSION_NAME = '3.4.2' group by PHONE_NUMBER having count(b.PHONE_NUMBER) = 1

but it doesn't work as expected

Upvotes: 0

Views: 1217

Answers (5)

user13581111
user13581111

Reputation: 35

Try this query it should work...

Select PHONE_NUMBER from TABLE_C where VERSION_NAME = '3.4.2' group by VERSION_NAME
having count(b.PHONE_NUMBER) = 1

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You can also use the WINDOWS function as follows:

select * from
(select t.*,
       sum(case when version_name = '3.4.2' then 1 end) over (partition by phone_number ) as sm,
       count(1) over (partition by phone_number ) as cnt
from your_table T)
where cnt = 1 and sm = 1

Upvotes: 0

VTi
VTi

Reputation: 1319

You can use this method where you are first getting the phone numbers which have one and only one appearance in the table and then you are comparing that data with phone numbers that are in version 3.4.2 . For e.g. Phone number 899589023200 will be a row in ph_count as the count is 1 and then 899589023200 also is in version 3.4.2 so when joined we get it in the output.

enter image description here

with test (id, phone_number, version_name) as
     (select 1, 86578900000 , '3.4.2'   union 
      select 2, 86578900000 , '3.4.2.1' union 
      select 3, 899589023200, '3.4.2'   union 
      select 4, 878999933336, '3.4.2'   union 
      select 5, 82199987629 , '3.4.2'   union 
      select 6, 82199987629 , '3.4.2.1' union 
      select 7, 888729203892, '3.4.2.1' 
     )

SELECT t.*
FROM test t
JOIN (SELECT phone_number, COUNT(*) as ph_count
      FROM 
      test t
      GROUP BY phone_number
      HAVING count(*) = 1) ph_cnt ON ph_cnt.phone_number = t.phone_number
WHERE t.version_name like '3.4.2'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You can use not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.phone_number = t.phone_number and
                        t2.version <> '3.4.2'
                 );

You can literally describe this query as: "Return all rows where the same phone number has no version other than '3.4.2'".

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142710

Here's one option:

SQL> with test (id, phone_number, version_name) as
  2    (select 1, 86578900000 , '3.4.2'   from dual union all
  3     select 2, 86578900000 , '3.4.2.1' from dual union all
  4     select 3, 899589023200, '3.4.2'   from dual union all
  5     select 4, 878999933336, '3.4.2'   from dual union all
  6     select 5, 82199987629 , '3.4.2'   from dual union all
  7     select 6, 82199987629 , '3.4.2.1' from dual union all
  8     select 7, 888729203892, '3.4.2.1' from dual
  9    )
 10  select a.id,
 11         a.phone_number
 12  from test a
 13  where a.version_name = '3.4.2'
 14    and a.phone_number in (select b.phone_number
 15                           from test b
 16                           group by b.phone_number
 17                           having count(*) = 1
 18                          );

        ID  PHONE_NUMBER
---------- -------------
         3  899589023200
         4  878999933336

SQL>

Condition in line #13 fetches only 3.4.2 version name, while subquery (lines #14 - 18) makes sure that phone numbers have only one appearance.

Upvotes: 0

Related Questions