CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user28455
user28455

Reputation: 455

Order by without top in SQL

I have a table Test with AcctNbr, Balance and Date columns. This table contains data as below

Acctnbr Balance LastUpdatedDt
123       100      May 08
456       200      May 08
222       300      May 07
333       400      May 07
123        50      May 06
222        120     May 05
678       70       May 04

Am trying to achieve daily balance as below

AcctNbr  Balance LastUpdatedDt
123       100      May 08
456       200      May 08
222       300      May 08
333       400      May 08
123        50      May 08
678        70      May 08
123        50      May 07
456       0        May 07
222       300      May 07
333       400      May 07
123        50      May 07
678        70      May 07
123        50      May 06
456       0        May 06
222       120      May 06
333       0       May 06
123        50      May 06
678        70      May 06
123        0      May 05
456       0        May 05
222       120      May 05
333       0       May 05
123        0      May 05
678        70      May 05
123        0      May 04
456       0        May 04
222       0      May 04
333       0       May 04
123        0      May 04
678        70      May 04

Here is my query 

 SELECT 
A.Accountnbr,
dt AS CalendarDate, 
A.Balance
FROM 
dbo.GetDates('20150504',GETDATE())
cross APPLY 
(
    SELECT TOP 1 Accountnbr,Balance 
    FROM dbo.Test
    WHERE LastUpdateDt <= dt 
    ORDER BY LastUpdateDt DESC
) A

This is working fine when i use TOP and Order BY inside CROSS APPLY. But i want to get balances for all accounts. I cannot use TOP inside CROSS APPLY as Test table will be getting updated every day with new accounts, but i need Order By to get correct balances from To and End dates in GetDates function. My question is how to use Order by without using TOP in the above query. Any help is appreciated.

Upvotes: 0

Views: 1106

Answers (1)

Neil P
Neil P

Reputation: 3190

I would use a cte with a row number and partition on account number and date (assuming you want the latest by account and day).

You can then join any tables onto the cte table if you need to.

with balance_cte as (
    SELECT Accountnbr, 
    LastUpdatedDt, 
    Balance,
    row_number() over (partition by  Accountnbr. mydate order by LastUpdateDt DESC) as rn
    FROM dbo.Test
 )

select Accountnbr,
LastUpdatedDt, 
Balance 
from  join balance_cte 
where rn = 1

Upvotes: 1

Related Questions