HiHow I can get last max syear for all stdDECLARE cash TABLE Id int smoney money syear char7 std char5 course intINSERT INTO cash Id smoney syear std course VALUES 1 2000 N3920171839 N39A000139 1INSERT INTO cash Id smoney syear std course VALUES 2 1000 N3920171839 N39A000139 1INSERT INTO cash Id smoney syear std course VALUES 3 1000 N3920171839 N39A000239 1INSERT INTO cash Id smoney syear std course

Hi PRAUse below querySQLDECLARE cash TABLE Id int smoney money syear char7 std char5 course intINSERT INTO cash Id smoney syear std course VALUES 1 2000 N3920171839 N39A000139 1INSERT INTO cash Id smoney syear std course VALUES 2 1000 N3920171839 N39A000139 1INSERT INTO cash Id smoney syear std course VALUES 3 1000 N3920171839 N39A000239 1INSERT INTO cash Id smoney syear std course VALUES 4 1000 N3920171839 N39A000239 1INSERT INTO cash Id smoney syear std course VALUES 5 1000 N3920171839 N39A000339 1INSERT INTO cash Id smoney syear std course VALUES 6 2000 N3920171839 N39A000339 1INSERT INTO cash Id smoney syear std course VALUES 7 500 N3920171839 N39A000139 1INSERT INTO cash Id smoney syear std course VALUES 8 500 N3920171839 N39A000239 1INSERT INTO cash Id smoney

Select rows with MAX column value in SQL Server

PRA

SOLVED CLOSED ACTIVE ANSWERED
User: PRA
Posted: on Aug 17, 2020 12:50 AM
Forum: SQL Server
Answer: 1
Views: 1383

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

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on Aug 17, 2020 06:02 AM
Report

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Select rows with MAX column value in SQL Server

Company

Explore

Follow us

Contact Us