Click here to Skip to main content
14,869,890 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Need SQL query on MAXIFS for Max of value column from the below table

Excel for formula for Max of value : =MAXIFS(B:B,A:A,A4,C:C,"Yes")

ABC	Level	IsValid	Max of Value
10D34AV	0	No	0
10D34AV	1	No	0
10D35AV	0	No	2
10D35AV	1	No	2
10D36AV	0	Yes	2
10D36AV	1	Yes	2
10D36AV	2	Yes	2
10D36AV	2	Yes	2
10D36AV	2	Yes	2
10D36AV	1	Yes	2
10D36AV	2	Yes	2
10D37AV	0	Yes	2
10D37AV	1	Yes	2
10D37AV	2	Yes	2
10D37AV	2	Yes	2
10D37AV	1	Yes	2
10D37AV	2	Yes	2
10D38AV	0	Yes	2
10D38AV	1	Yes	2
10D38AV	2	Yes	2
10D38AV	2	Yes	2
10D38AV	2	Yes	2
10D38AV	1	Yes	0
10D38AV	2	Yes	0
10D39AV	0	No	0
10D39AV	1	No	2
10D39AV	2	No	2
10D40AV	0	Yes	2
10D40AV	1	Yes	2
10D40AV	2	Yes	0
10D40AV	2	Yes	0


What I have tried:

I tried with MAX function in sql but not getting correct results
Posted
Updated 21-Mar-21 21:05pm
v2

When I try your formula in Excel - to try and work out what you want to generate - I get 0
And that doesn't look like any sensible result to me, though I'm not sure exactly what you expect it to generate.

I don't know if I have your data wrong, or your Excel function is bad - but something is looking very odd about it: MAXIFS function - Office Support[^]
   
Comments
Maciej Los 22-Mar-21 2:59am
   
5ed!
As OriginalGriff mentioned, there's something wrong with your data... Neverthless...
MAXIFS in Excel corresponds with MAX() window function in SQL. See:

SQL
CREATE TABLE Test
(
  ABC varchar(30),
  Level int,
  IsValid varchar(30)
);

INSERT INTO Test(ABC, Level, IsValid)
VALUES ('10D34AV', 0, 'No'),
('10D34AV', 1, 'No'),
('10D35AV', 0, 'No'),
('10D35AV', 1, 'No'),
('10D36AV', 0, 'Yes'),
('10D36AV', 1, 'Yes'),
('10D36AV', 2, 'Yes'),
('10D36AV', 2, 'Yes'),
('10D36AV', 2, 'Yes'),
('10D36AV', 1, 'Yes'),
('10D36AV', 2, 'Yes'),
('10D37AV', 0, 'Yes'),
('10D37AV', 1, 'Yes'),
('10D37AV', 2, 'Yes'),
('10D37AV', 2, 'Yes'),
('10D37AV', 1, 'Yes'),
('10D37AV', 2, 'Yes'),
('10D38AV', 0, 'Yes'),
('10D38AV', 1, 'Yes'),
('10D38AV', 2, 'Yes'),
('10D38AV', 2, 'Yes'),
('10D38AV', 2, 'Yes'),
('10D38AV', 1, 'Yes'),
('10D38AV', 2, 'Yes'),
('10D39AV', 0, 'No'),
('10D39AV', 1, 'No');


SELECT ABC, Level, IsValid, MAX(Level) OVER(PARTITION BY ABC ORDER BY ABC) MaxOfLevel
FROM Test


Result: SQL Server 2019 | db<>fiddle[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900