In SQL return rows with max value for each group, including rows that have the same value

How do I return rows with the max Value for each department and if two or more rows for the same Department have the same Value include those in the results?

For instance, I have the table below:

Name	Code	Date	Value
Finance	ABC12	2020-05-12	15
Finance	ABC12	2020-06-24	118
Marketing	BBG43	2020-05-12	12
Marketing	BBG43	2021-08-07	1
Tech	PKY76	2020-07-21	7
Tech	PKY76	2020-11-28	7

I want to return rows that have the maximum Value for each department. If a department has the same maximum Value on different days return all of those days.

For example, my desired output would be this:

Name	Code	Date	Value
Finance	ABC12	2020-06-24	118
Marketing	BBG43	2020-05-12	12
Tech	PKY76	2020-07-21	7
Tech	PKY76	2020-11-28	7

So far I have this code:

SELECT 
Name
,Code
,MAX(Date)
,MAX(Value) AS Total 
FROM Department 
GROUP BY 
Name
,Code

Obviously, the issue with this is that I don't want to get just the MAX date, but I don't know how to include the Date in the output without using it in the GROUP BY. As a result, I'm only getting the results for each department with the latest date, in the case of multiple rows with the same department only one is included.

Solution

you can try this

CREATE TABLE Department (
name VARCHAR(50) NOT NULL,
code VARCHAR(100) NULL,
date DATETIME NULL,
Value INT NULL
);
INSERT INTO Department VALUES('Finance', 'ABC12', '2020-05-12', 15);
INSERT INTO Department VALUES('Finance', 'ABC12', '2020-06-24', 118);
INSERT INTO Department VALUES('Marketing', 'BBG43', '2020-05-12', 12);
INSERT INTO Department VALUES('Marketing', 'BBG43', '2021-08-07', 1);
INSERT INTO Department VALUES('Tech', 'PKY76', '2020-07-21', 7);
INSERT INTO Department VALUES('Tech', 'PKY76', '2020-11-28', 7);


SELECT D.Name, D.Code, D.Date, T.MAX_VALUE 
FROM Department D
INNER JOIN (
SELECT Name,Code,MAX(Value) AS MAX_VALUE 
FROM Department 
GROUP BY Name,Code
) T ON D.CODE=T.CODE AND D.Value = T.MAX_VALUE;

And in future please share the create table and insert scripts. It is easy for us to provide solution.