15,309,563 members
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

## Solution 1

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[^]
Maciej Los 22-Mar-21 2:59am

5ed!

## Solution 2

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[^]