MIN I know will not work here cause of the debit adding to the balance or TOP 1 didn't work on balance that has negative like in the red box the result I'm getting is (-156152.33) instead of (443847.47) and how can I get the (bottom)balance of each GROUP akawnt.

SELECT AccountTitle as akawnt, [Year], DepartmentName as depar, Debit, Credit
    , Balance as balanse
FROM YearlyBudget 
WHERE DepartmentName = DepartmentName
AND MotherTitle = MotherTitle
AND AccountTitle = AccountTitle
GROUP BY AccountTitle, [Year], DepartmentName, Debit, Credit, Balance 

assume that there's another account and department in the same table that's GROUP BY

SELECT akawnt, MIN(balanse)
FROM (
    SELECT AccountTitle as akawnt, [Year], DepartmentName as depar
        , Debit, Credit, Balance as balanse
    FROM YearlyBudget 
    WHERE DepartmentName = DepartmentName
    AND MotherTitle = MotherTitle
    AND AccountTitle = AccountTitle
    GROUP BY AccountTitle, [Year], DepartmentName, Debit, Credit, Balance 
) src
GROUP BY akawnt

ACTUAL OUTPUT:

DESIRED OUTPUT:

MIN does not applicable too when you add to Debit I like to get the latest balance.

CodePudding user response：

You can use the row_number function to select a single row, but you need a column to order it by - I have added an identity column to demonstrate but I hope you have a real datetime column you can use.

declare @YearlyBudget table (id int identity(1,1), akawnt varchar(12), DepartmentName varchar(12), Debit money, Credit money, Balance money);

insert into @YearlyBudget (akawnt, DepartmentName, Debit, Credit, Balance)
values
('Account1', 'Department1', 411971.35, null, 411971.35),
('Account1', 'Department1', null, 41666.31, 370305.04),
('Account1', 'Department1', null, 46763.47, 323541.57),
('Account1', 'Department1', null, 116549.53, 206992.04),
('Account1', 'Department1', null, 60668.01, 146324.03),
('Account1', 'Department1', null, 113003.92, 33320.11),
('Account1', 'Department1', null, 63651.79, -30331.68),
('Account1', 'Department1', null, 68005.37, -98337.05),
('Account1', 'Department1', null, 57815.28, -156152.33),
('Account1', 'Department1', 600000.00, null, 443847.67),
('Account2', 'Department2', 2351500.00, null, 2351500.00),
('Account2', 'Department2', null, 1178500.00, 1173000.00),
('Account2', 'Department2', null, 2000.00, 1171000.00),
('Account2', 'Department2', 1159500.00, 1159500.00, 2330500.00);

WITH cte AS (
    SELECT akawnt, Balance
        -- You need a way to order your rows, I have used an id as an example but I suspect you have a date column to use here
        , ROW_NUMBER() OVER (PARTITION BY akawnt ORDER BY id) rn
    FROM @YearlyBudget 
)
SELECT akawnt, Balance
FROM cte
WHERE rn = 1;

Note: if you add your sample data as DDL DML (as I have shown here) you make it much easier to answer. And you ensure you have a working minimal reproducible example (there were syntax errors in your example.