sql - Returning accounts that have 5 clients (only way to determine is by the number assigned to the client), how do I return al

admin管理员组

文章数量:1312946

This isn't my database. I can only query and return the results based on the request provided to me.

Let's say our database has

• Account Number: Acct
• Multiple clients are possible on same account, so which # client on the account: CNO
• First Name: Fname
• Customer specific ID: CID

More details would exist but just to keep it simple.

The CNO field is essentially tracking the order the specific client was added to the account. I can't guarantee that the clients were added at the same time, so they may appear in different rows on the actual database. Order by Acct will obviously address that.

Acct, CNO, FName, CID.

I want to find specific accounts that have 5 clients and return all 5 rows for those accounts.

If possible, how would I return accounts having CNO values 1-5 while limiting the return to only those accounts that actually have a client with 5 in the CNO field. I don't want to return any account with clients 4 or less nor 6 or more.

select *
from database
where CNO = 5
order by Acct
fetch first 200 rows only

This is going to pull only that 5th client, not 1-4 and it would include the #5 clients from accounts having 6 or more.

I would love to have been able to add a another column with client count that updated to the highest CNO value an account has and then filter on that. Querying only, without having write access to the database, is this even possible? I am using Oracle SQL Developer. In addition, making it something usable later in a separate GUI, trick would be to find a way to keep the runtime low.

Thanks in advance!

This isn't my database. I can only query and return the results based on the request provided to me.

Let's say our database has

• Account Number: Acct
• Multiple clients are possible on same account, so which # client on the account: CNO
• First Name: Fname
• Customer specific ID: CID

More details would exist but just to keep it simple.

The CNO field is essentially tracking the order the specific client was added to the account. I can't guarantee that the clients were added at the same time, so they may appear in different rows on the actual database. Order by Acct will obviously address that.

Acct, CNO, FName, CID.

I want to find specific accounts that have 5 clients and return all 5 rows for those accounts.

If possible, how would I return accounts having CNO values 1-5 while limiting the return to only those accounts that actually have a client with 5 in the CNO field. I don't want to return any account with clients 4 or less nor 6 or more.

select *
from database
where CNO = 5
order by Acct
fetch first 200 rows only

This is going to pull only that 5th client, not 1-4 and it would include the #5 clients from accounts having 6 or more.

I would love to have been able to add a another column with client count that updated to the highest CNO value an account has and then filter on that. Querying only, without having write access to the database, is this even possible? I am using Oracle SQL Developer. In addition, making it something usable later in a separate GUI, trick would be to find a way to keep the runtime low.

Thanks in advance!

• sql
• oracle-database

Share Improve this question Follow edited Jan 31 at 22:15 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Jan 31 at 20:51 Jordon GonzalesJordon Gonzales 18311 silver badge88 bronze badges 5

• 1 For SQL related questions please provide a minimal reproducible example with sample data, desired results and your query. Text descriptions struggle to illustrate data problems clearly compared to an actual example. – Dale K Commented Jan 31 at 22:17
• Is CNO guaranteed to be sequential from 1 to 5? What happens if a client is removed from an account, does it renumber the rest? – Barmar Commented Jan 31 at 22:38
• @DaleK I'll try to update it later this evening if I can get time. Thanks. – Jordon Gonzales Commented Jan 31 at 22:50
• @Barmar Nature of this database / job, the client would remain attached to the account. To be "removed," they'd have to close the account for all clients and open a new one minus the client who wished to be left off. – Jordon Gonzales Commented Jan 31 at 22:50
• What a crazy design! That means all references to the account would also have to be updated to point to the new account. Lots of violations of database normalization principles. – Barmar Commented Jan 31 at 22:54

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

Yes, you can! You need to use a window function, which is like a subquery in the main query where you can get the values you need to make the query work. The solution you're looking for will look like this:

SELECT *
FROM (
    SELECT d.*,
           MAX(CNO) OVER (PARTITION BY Acct) AS MaxCNO
    FROM database d
)
WHERE MaxCNO = 5
  AND CNO BETWEEN 1 AND 5
ORDER BY Acct, CNO;

As you can see, MAX(CNO) gets the maximum value for each account and stores it in a variable called MaxCNO. Then, you can use it to filter the data as you need.

If I got it right you have ordered CNO column values and you ask for the rows of ACCT having exactly 5 CNO values (1, 2, 3, 4, 5). That could be a table like here:

PART 1 - ordered values:

--      S a m p l e    D a t a :
Create Table tbl AS
Select 101 as ACCT, 1 as CNO, 'A' as FNAME, 11 as CID, SYSDATE-8 as CDATE From Dual Union All 
Select 101, 2, 'A', 11, SYSDATE - 7 From Dual Union All 
Select 101, 3, 'A', 11, SYSDATE - 6 From Dual Union All 
Select 101, 4, 'A', 11, SYSDATE - 5 From Dual Union All 
Select 101, 5, 'A', 11, SYSDATE - 4 From Dual Union All 
Select 101, 6, 'A', 11, SYSDATE - 3 From Dual Union All 
Select 101, 7, 'A', 11, SYSDATE - 2 From Dual Union All 
--
Select 201, 1, 'B', 21, SYSDATE - 10 From Dual Union All 
Select 201, 2, 'B', 21, SYSDATE -  8 From Dual Union All
Select 201, 3, 'B', 21, SYSDATE -  5 From Dual Union All
Select 201, 4, 'B', 21, SYSDATE -  2 From Dual Union All
Select 201, 5, 'B', 21, SYSDATE -  1 From Dual;

... here are two queries fetching the 5 rows as asked and resulting the same (ACCT 201). Please ignore the last two columns for now (not needed for this part) - they are here for comparison with queries below dealing with possibility of unordered CNO values and/or different understanding of the question.

... first one uses correlated subquery in the Where clause ...

--    SQL_1
Select     t.*, 
           Max(CNO) Over (Partition By ACCT) AS CNO_MAX, 
           Count(Distinct CNO) Over (Partition By ACCT) AS CNO_DIST_CNT, 
           Count(Case When CNO = 5 Then 1 End) Over (Partition By ACCT) as EXIST_5
From       tbl t
Where    ( Select MAX(CNO) From tbl Where ACCT = t.ACCT ) = 5
Order By   t.ACCT, CNO;

... second one uses Inner Join to aggregated values filtered by Having clause...

--    SQL_2
Select       t.*, t5.CNO_MAX, t5.CNO_DIST_CNT, 
             Count(Case When t.CNO = 5 Then 1 End) Over (Partition By t.ACCT) as EXIST_5
From         tbl t
Inner Join ( Select ACCT, MAX(CNO) as CNO_MAX, Count(Distinct CNO) as CNO_DIST_CNT 
             From tbl 
             Group By ACCT 
             Having MAX(CNO) = 5 ) t5 ON(t5.ACCT = t.ACCT)
Order By     t.ACCT, t.CNO;

both resulting as

ACCT	CNO	FNAME	CID	CDATE	CNO_MAX	CNO_DIST_CNT	EXIST_5
201	1	B	21	22-JAN-25	5	5	1
201	2	B	21	24-JAN-25	5	5	1
201	3	B	21	27-JAN-25	5	5	1
201	4	B	21	30-JAN-25	5	5	1
201	5	B	21	31-JAN-25	5	5	1

PART 2 - unordered values:
If we insert some rows with unordered values in CNO column ...

Insert Into tbl 
Select 301, 1, 'B', 31, SYSDATE - 12 From Dual Union All 
Select 301, 2, 'B', 31, SYSDATE -  8 From Dual Union All
Select 301, 3, 'B', 31, SYSDATE -  4 From Dual Union All
Select 301, 5, 'B', 31, SYSDATE -  3 From Dual Union All
Select 301, 7, 'B', 31, SYSDATE -  1 From Dual Union All
--
Select 401, 1, 'D', 41, SYSDATE - 6 From Dual Union All 
Select 401, 2, 'D', 41, SYSDATE - 3 From Dual Union All 
Select 401, 5, 'D', 41, SYSDATE - 1 From Dual;

... the above queries' resultsets will change - both fetching ACCT 401 for having Max(CNO) = 5 and not fetching ACCT 301 which has exactly 5 different CNO values but the max one is 7

--    SQL_1 -> affected by inserted unordered rows (ACCT 401  has 5 as max in CNO column)
Select     t.*, 
           Max(CNO) Over (Partition By ACCT) AS CNO_MAX, 
           Count(Distinct CNO) Over (Partition By ACCT) AS CNO_DIST_CNT, 
           Count(Case When CNO = 5 Then 1 End) Over (Partition By ACCT) as EXIST_5
From       tbl t
Where    ( Select MAX(CNO) From tbl Where ACCT = t.ACCT ) = 5
Order By   t.ACCT, CNO;

... and

--    SQL_2  -> affected by inserted unordered rows (ACCT 401 has 5 as max in CNO column)
Select       t.*, t5.CNO_MAX, t5.CNO_DIST_CNT, 
             Count(Case When t.CNO = 5 Then 1 End) Over (Partition By t.ACCT) as EXIST_5
From         tbl t
Inner Join ( Select ACCT, MAX(CNO) as CNO_MAX, Count(Distinct CNO) as CNO_DIST_CNT 
             From tbl 
             Group By ACCT 
             Having MAX(CNO) = 5 ) t5 ON(t5.ACCT = t.ACCT)
Order By     t.ACCT, t.CNO;

resulting the same (with ACCT 401) ...

ACCT	CNO	FNAME	CID	CDATE	CNO_MAX	CNO_DIST_CNT	EXIST_5
201	1	B	21	22-JAN-25	5	5	1
201	2	B	21	24-JAN-25	5	5	1
201	3	B	21	27-JAN-25	5	5	1
201	4	B	21	30-JAN-25	5	5	1
201	5	B	21	31-JAN-25	5	5	1
401	1	D	41	26-JAN-25	5	3	1
401	2	D	41	29-JAN-25	5	3	1
401	5	D	41	31-JAN-25	5	3	1

... adjustments depend on whether you want the same result as with just ordered CNO values or not ....
SQL_1

--    SQL_1  -> changed to fetch the same rows as before insertion of unordered rows
Select     t.*, 
           Max(CNO) Over (Partition By ACCT) AS CNO_MAX, 
           Count(Distinct CNO) Over (Partition By ACCT) AS CNO_DIST_CNT, 
           Count(Case When CNO = 5 Then 1 End) Over (Partition By ACCT) as EXIST_5
From       tbl t
Where    ( Select To_Char(MAX(CNO)) || ', ' || To_Char(Count(Distinct CNO)) as MAX_CNT
           From tbl Where ACCT = t.ACCT ) = '5, 5'
Order By   t.ACCT, CNO;

ACCT	CNO	FNAME	CID	CDATE	CNO_MAX	CNO_DIST_CNT	EXIST_5
201	1	B	21	22-JAN-25	5	5	1
201	2	B	21	24-JAN-25	5	5	1
201	3	B	21	27-JAN-25	5	5	1
201	4	B	21	30-JAN-25	5	5	1
201	5	B	21	31-JAN-25	5	5	1

--    SQL_1  -> changed to fetch the same rows as before insertion plus ACCT 301
--              which has exactly 5 distinct CNOs
Select     t.*, 
           Max(CNO) Over (Partition By ACCT) AS CNO_MAX, 
           Count(Distinct CNO) Over (Partition By ACCT) AS CNO_DIST_CNT, 
           Count(Case When CNO = 5 Then 1 End) Over (Partition By ACCT) as EXIST_5
From       tbl t
Where    ( Select Count(Distinct CNO) as CNO_DIST_CNT
           From tbl Where ACCT = t.ACCT ) = 5
Order By   t.ACCT, CNO;

ACCT	CNO	FNAME	CID	CDATE	CNO_MAX	CNO_DIST_CNT	EXIST_5
201	1	B	21	22-JAN-25	5	5	1
201	2	B	21	24-JAN-25	5	5	1
201	3	B	21	27-JAN-25	5	5	1
201	4	B	21	30-JAN-25	5	5	1
201	5	B	21	31-JAN-25	5	5	1
301	1	B	31	20-JAN-25	7	5	1
301	2	B	31	24-JAN-25	7	5	1
301	3	B	31	28-JAN-25	7	5	1
301	5	B	31	29-JAN-25	7	5	1
301	7	B	31	31-JAN-25	7	5	1

SQL_2

--    SQL_2  -> changed to work like before insertion of unordered rows 
Select       t.*, t5.CNO_MAX, t5.CNO_DIST_CNT, 
             Count(Case When t.CNO = 5 Then 1 End) Over (Partition By t.ACCT) as EXIST_5
From         tbl t
Inner Join ( Select ACCT, MAX(CNO) as CNO_MAX, Count(Distinct CNO) as CNO_DIST_CNT 
             From tbl 
             Group By ACCT 
             Having MAX(CNO) = 5 And 
                    Count(Distinct CNO) = 5) t5 ON(t5.ACCT = t.ACCT)
Order By     t.ACCT, t.CNO
    

ACCT	CNO	FNAME	CID	CDATE	CNO_MAX	CNO_DIST_CNT	EXIST_5
201	1	B	21	22-JAN-25	5	5	1
201	2	B	21	24-JAN-25	5	5	1
201	3	B	21	27-JAN-25	5	5	1
201	4	B	21	30-JAN-25	5	5	1
201	5	B	21	31-JAN-25	5	5	1

--    SQL_2  -> changed to work like before insertion of unordered rows plus ACCT 301
--              which has exactly 5 distinct CNOs
Select       t.*, t5.CNO_MAX, t5.CNO_DIST_CNT, 
             Count(Case When t.CNO = 5 Then 1 End) Over (Partition By t.ACCT) as EXIST_5
From         tbl t
Inner Join ( Select ACCT, MAX(CNO) as CNO_MAX, Count(Distinct CNO) as CNO_DIST_CNT 
             From tbl 
             Group By ACCT 
             Having Count(Distinct CNO) = 5) t5 ON(t5.ACCT = t.ACCT)
Order By     t.ACCT, t.CNO

ACCT	CNO	FNAME	CID	CDATE	CNO_MAX	CNO_DIST_CNT	EXIST_5
201	1	B	21	22-JAN-25	5	5	1
201	2	B	21	24-JAN-25	5	5	1
201	3	B	21	27-JAN-25	5	5	1
201	4	B	21	30-JAN-25	5	5	1
201	5	B	21	31-JAN-25	5	5	1
301	1	B	31	20-JAN-25	7	5	1
301	2	B	31	24-JAN-25	7	5	1
301	3	B	31	28-JAN-25	7	5	1
301	5	B	31	29-JAN-25	7	5	1
301	7	B	31	31-JAN-25	7	5	1

fiddle

本文标签：