Select max record from each group in SQL Server

Last Reply 2 months ago By dharmendr

Posted 2 months ago

Hi!

How to get max years and min one number

I used below script its working when all std has max syear. When one or two std has max syear then not shows others.

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

I want this result:

money

syear

course

std

3000,00

2018/19

2

A0002

3000,00

2018/19

2

A0003

3500,00

2019/20

3

A0001

Posted 2 months ago

Hi PRA,

Refer below query.

SQL

DECLARE @cash TABLE (Id int, smoney money, syear char(7), std char(5), course int, pay int)
INSERT INTO @cash VALUES (1, 2000, N'2017/18', N'A0001', 1, 0)
INSERT INTO @cash VALUES (2, 1000, N'2017/18', N'A0001', 1, 0)
INSERT INTO @cash VALUES (3, 1000, N'2017/18', N'A0002', 1, 0)
INSERT INTO @cash VALUES (4, 1000, N'2017/18', N'A0002', 1, 0)
INSERT INTO @cash VALUES (5, 1000, N'2017/18', N'A0003', 1, 0)
INSERT INTO @cash VALUES (6, 2000, N'2017/18', N'A0003', 1, 0)
INSERT INTO @cash VALUES (7, 500, N'2017/18', N'A0001', 1, 0)
INSERT INTO @cash VALUES (8, 500, N'2017/18', N'A0002', 1, 0)
INSERT INTO @cash VALUES (9, 500, N'2018/19', N'A0002', 2, 0)
INSERT INTO @cash VALUES (10, 500, N'2018/19', N'A0001', 2, 0)
INSERT INTO @cash VALUES (11, 500, N'2018/19', N'A0003', 2, 0)
INSERT INTO @cash VALUES (12, 2500, N'2018/19', N'A0003', 2, 0)
INSERT INTO @cash VALUES (13, 2500, N'2018/19', N'A0002', 2, 0)
INSERT INTO @cash VALUES (14, 2500, N'2018/19', N'A0001', 2, 0)
INSERT INTO @cash VALUES (15, 1500, N'2019/20', N'A0001', 3, 0)
INSERT INTO @cash VALUES (20, 2000, N'2019/20', N'A0001', 3, 0)
   
SELECT money,syear,course,std 
FROM
(
	SELECT ROW_NUMBER() OVER (PARTITION BY (std) ORDER BY syear DESC) Row_No,
	CASE pay WHEN 0 THEN SUM(smoney) END 'money', syear, course, std
	FROM @cash
	WHERE syear IN (SELECT DISTINCT MAX(syear) FROM @cash GROUP BY std,course) AND pay = 0
	GROUP BY syear, std, course, pay
)t
WHERE Row_No = 1
ORDER BY course

Output

money syear course std
3000 2018/19 2 A0002
3000 2018/19 2 A0003
3500 2019/20 3 A0001