Query Syntax

can you provide some sample data?

and what database are you using?

Below query will give you proper output you need.  For this I am using count(*) Over (Partition by client order by account) so that we can count how many accounts each client has

SELECT * FROM 
(
SELECT * , COUNT(*) OVER (PARTITION BY client order by account) cnt
FROM yourtable 
)r 
WHERE cnt = 1

Select allOpen in new window

It is oracle Db. Below is the sample data. I need client which has only one account, here it is client 2 , so I need the 3 row of this table

Client Number     Account Number     Balance
C1                           A123                          100
C1                           A546                          50
C2                           A65456                      30

Please try this -

SELECT y.* FROM 
(
	SELECT [Client Number] FROM 
	(
		SELECT [Client Number] , COUNT(*) cnt
		FROM yourtable 
		GROUP BY [Client Number]
	)r 
	WHERE cnt = 1
)o INNER JOIN yourtable y ON O.[Client Number] = y.[Client Number]

Select allOpen in new window

Output

/*------------------------
OUTPUT
------------------------*/
Client Number Account Number Balance
------------- -------------- -----------
C2            A65456         30

(1 row(s) affected)

Select allOpen in new window

For ORACLE  please use below

You can adjust the column names.

WITH CTE AS 
(
    SELECT 'C1' c ,'A123' a , 100 b
    FROM DUAL  UNION ALL
    SELECT 'C1','A546',50  FROM DUAL UNION ALL
    SELECT 'C2','A65456',30  FROM DUAL
)
,CTE1 AS
(
    SELECT c FROM 
    (SELECT c , COUNT(*) cnt
    FROM CTE 
    GROUP BY c) WHERE cnt = 1
)
SELECT k1.* FROM CTE1 k INNER JOIN CTE k1 ON k1.c = k.c

Select allOpen in new window

OUTPUT

 	C	A	B
1	C2	A65456	30

Select allOpen in new window

a similar solution using Group By with Having clause.

create table mytable (
    Client_Number varchar2(4000),
    Account_Number varchar2(4000),
    Balance number
);
/

insert into mytable (Client_Number, Account_Number, Balance)
select 'C1', 'A123', 100 from dual union all
select 'C1', 'A546', 50 from dual union all
select 'C2', 'A65456', 30 from dual;
/

select a.* from mytable a
inner join (
select Client_Number from mytable group by Client_Number having count(*) = 1
) b on a.Client_Number = b.Client_Number

Select allOpen in new window

another simple and tested method is given below -

Explanation - Just count how many accounts you have per client and take client who has only 1 account using -  COUNT(*) OVER(PARTITION BY c) as cnt

WITH CTE AS 
(
    SELECT 'C1' c ,'A123' a , 100 b
    FROM DUAL  UNION ALL
    SELECT 'C1','A546',50  FROM DUAL UNION ALL
    SELECT 'C2','A65456',30  FROM DUAL
)
SELECT c clientNumber ,a accountNumber , b Balance
FROM 
(
    SELECT c,b,a , COUNT(*) OVER(PARTITION BY c) as cnt FROM CTE

)WHERE cnt = 1 

Select allOpen in new window

Output

 	CLIENTNUMBER	ACCOUNTNUMBER	BALANCE
1	C2	A65456	30

Select allOpen in new window

@Pawan: Thanks for your help.
I have one issue: in the WITH CTE clause, how will I select the data? c1, A123 this is data...should I give column names here?

select * from yourtable where clientnumber in
(select clientnumber from
 (select clientnumber, count(*) from yourtable group by clientnumber having count(*) = 1)
)

you don't need to supply the count(*) in the column names ...

select * from yourtable where clientnumber in
 (select clientnumber from yourtable group by clientnumber having count(*) = 1)

Select allOpen in new window

Thanks for your help guys. My problem solved

welcome. glad to help as always.