Select the next greatest value from a column

user_15357457 0
Viewed 64

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

CompanyId AccountId CustomerId
1 14 2
1 14 5
1 17 3
1 18 1
1 18 2
2 7 3
2 7 4
2 25 1

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

CompanyId AccountId CustomerId NextAccountId
1 14 2 17
1 14 5 17
1 17 3 18
1 18 1 NULL
1 18 2 NULL
2 7 3 25
2 7 4 25
2 25 1 NULL
tsql sql-server sql
2 Answers

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;
user_153326500

You can do this entirely with window functions, and only scanning the base table once.

SELECT *,
  NextAccount = MAX(NextRow) OVER (PARTITION BY CompanyId, AccountId)
FROM (
    SELECT *,
      NextRow = NULLIF(LEAD(AccountId) OVER (PARTITION BY CompanyId ORDER BY AccountId), AccountId)
    FROM Table1
) t

We use LEAD sorted by AccountId to get the next row's value. We null it out if it equals the current row.

Then MAX or MIN of that, while partitioning (not sorting) by AccountId, gets us the only non-null value in the partition.

db<>fiddle

user_148689970
Related