Select rows with MAX column value in SQL Server

Last Reply 3 months ago By dharmendr

Posted 3 months ago

Hi!

How I can get last max syear for all std?

DECLARE @cash TABLE (Id int, smoney money, syear char(7), std char(5), course int)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (1, 2000, N'2017/18', N'A0001', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (2, 1000, N'2017/18', N'A0001', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (3, 1000, N'2017/18', N'A0002', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (4, 1000, N'2017/18', N'A0002', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (5, 1000, N'2017/18', N'A0003', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (6, 2000, N'2017/18', N'A0003', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (7, 500, N'2017/18', N'A0001', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (8, 500, N'2017/18', N'A0002', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (9, 500, N'2018/19', N'A0002', 2)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (10, 500, N'2018/19', N'A0001', 2)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (11, 500, N'2018/19', N'A0003', 2)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (12, 2500, N'2018/19', N'A0003', 2)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (13, 2500, N'2018/19', N'A0002', 2)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (14, 2500, N'2018/19', N'A0001', 2)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (15, 1500, N'2019/20', N'A0001', 3)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (16, 1500, N'2019/20', N'A0002', 3)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (17, 1500, N'2019/20', N'A0003', 3)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (18, 2000, N'2019/20', N'A0003', 3)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (19, 2000, N'2019/20', N'A0002', 3)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (20, 2000, N'2019/20', N'A0001', 3)

select sum(smoney)'money', syear, std, course from @cash group by syear, std, course

I want get below result:

money

syear

std

course

3500,00

2019/20

A0001

3

3500,00

2019/20

A0002

3

3500,00

2019/20

A0003

3

Posted 3 months ago

Hi PRA,

Use below query.

SQL

DECLARE @cash TABLE (Id int, smoney money, syear char(7), std char(5), course int)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (1, 2000, N'2017/18', N'A0001', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (2, 1000, N'2017/18', N'A0001', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (3, 1000, N'2017/18', N'A0002', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (4, 1000, N'2017/18', N'A0002', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (5, 1000, N'2017/18', N'A0003', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (6, 2000, N'2017/18', N'A0003', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (7, 500, N'2017/18', N'A0001', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (8, 500, N'2017/18', N'A0002', 1)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (9, 500, N'2018/19', N'A0002', 2)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (10, 500, N'2018/19', N'A0001', 2)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (11, 500, N'2018/19', N'A0003', 2)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (12, 2500, N'2018/19', N'A0003', 2)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (13, 2500, N'2018/19', N'A0002', 2)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (14, 2500, N'2018/19', N'A0001', 2)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (15, 1500, N'2019/20', N'A0001', 3)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (16, 1500, N'2019/20', N'A0002', 3)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (17, 1500, N'2019/20', N'A0003', 3)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (18, 2000, N'2019/20', N'A0003', 3)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (19, 2000, N'2019/20', N'A0002', 3)
INSERT INTO @cash (Id, smoney, syear, std, course) VALUES (20, 2000, N'2019/20', N'A0001', 3)
 
select sum(smoney)'money', syear, std, course 
from @cash 
WHERE syear = (SELECT MAX(syear) FROM @cash)
group by syear, std, course