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
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
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!
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