CODEWITHSUNDEEP
sql
paul
paul

Reputation: 135

How to make sure results from my query are continuous

I want to query a table that has account numbers but I need a sequential range of 60000 starting from any number, it doesn't matter as long as it is sequential.

I cannot come up with anything, I have just a basic between query but this is not even good because I don't want or need to specify a start, just need the sequential range. 

Select *
From tableA
where account number between acctNum1 and acctNum2;

A continuous range of 60000 account numbers.

So if there are account numbers from 1 to 1 million and there are random gaps in the account numbers, I want to see if I can get a qty of 60000 but sequential.

Upvotes: 0

Views: 301

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

Use COUNT OVER with a range window clause to see how many rows are in the range of account_number - 59999 and itself. Pick the first number that has a full count of 60000 and select from the table accordingly.

select *
from mytable
where 
(
  select min(account_number)
  from
  (
    select
      account_number,
      count(*) over (order by account_number range between 59999 preceding and current row) as cnt
    from mytable
  ) viewed
  where cnt = 60000
) between account_number and account_number + 59999
order by account_number;

Upvotes: 0

xQbert
xQbert

Reputation: 35343

Maybe something like this:

The below returns a list of all possible ranges having 60,000 sequential records.

it returns 3 columns a StartAcct#, a RN and a EndAcct#. The start and end's define the range you could use.

WITH CTE2 as (SELECT *, row_number() over (order by accountNumber) RN FROM SrcTable)
   , CTE3 as (SELECT AccountNumber as StartAcct#
                   , RN, LEAD(AccountNumber,60000-1) over(order by rn) as EndAcct#
              FROM CTE2 A)

SELECT * 
FROM CTE3
WHERE EndAcct#-StartAcct# = 60000-1

For example may return: (if gap of 20 is what you're looking for)...

+----+------------+----+----------+
|    | StartAcct# | RN | EndAcct# |
+----+------------+----+----------+
|  1 |         26 | 24 |       46 |
|  2 |         27 | 25 |       47 |
|  3 |         28 | 26 |       48 |
+----+------------+----+----------+
  • Adjust SrcTable to be table with accountNUmber
  • Assumes AccountNumber is actually numeric, you indicated started w/ 1 and went up sequentially by 1 from there...
  • assumes you have lead window function/analytic available to you.

Not too sure about performance... as materializing a cte for 1,000,000 records and assigning a row number to each may not be the most efficient thing in the world...

Rextester Example using 100 records with a gap returns all records having a gap of 20, adjusting 20 to 60,000 is pretty straight forward and the 20 was just a proof of concept.

Now that we have a start and end range, we can get the 60,000 records in question. Simply by querting the base table where the accountNumber is between start and end... by cross joining to the CTE3 having limited it to just 1 record...

Notes: Does SQL Server TOP stop processing once it finds enough rows? Seems to indicate Top may early exit if you're just after a first occurrence... but I find that hard to believe since an order has to be applied...

So testing would have to be done to know if top or limit could benefit the query.

Other attempts to answer feel free to leverage sample created!

Upvotes: 2

paul
paul

Reputation: 135

Before I was able to try the above answer I found this and it worked great for me. Thank you to everyone!

Find ranges from a series of numbers in SQL/Oracle

Upvotes: 0

Related Questions