I'm using SQL server and I have a table that looks like this (Table1). I want to look ahead to the next greatest AccountId value in the table by running a select statement.

Table1

I've tried using lead() but that's not doing what I want. It's only looking ahead to the next 'AccountId', not the next greatest. I only want to look ahead for the same 'CompanyId' and every time the 'AccountId' changes.

Select lead(AccountId, 1) over (partition by CompanyId order by CompanyId, CustomerId) as NextAccountId
From Table1

This is my desired outcome

CodePudding user response：

This can be done with a simple correlated sub-query:

select *, (
  select Min(t2.accountId) 
    from t t2 
    where t2.CompanyId=t.CompanyId and t2.accountId > t.accountId 
) as NextAccountId
from t;

CodePudding user response：

You can self join the table on AccountId being greater than the original table, and then use ROW_NUMBER() to partition over CompanyId, AccountId, and CustomerId to get the next value.

*This is under the assumption that the combination of those 3 columns will be unique:

with vals as (
select t1.CompanyId
    , t1.AccountId
    , t1.CustomerId
    , t2.AccountId NextAccountId
    , ROW_NUMBER() over (partition by t1.CompanyId, t1.AccountId, t1.CustomerId order by t1.CompanyId, t1.AccountId, t2.AccountId) rn
from Table1 t1
left join Table1 t2
    on t1.CompanyId = t2.CompanyId
    and t1.AccountId < t2.AccountId
)
select CompanyId
    , AccountId
    , CustomerId
    , NextAccountId
from vals
where rn = 1