---
title: "Subquery with MAX function SQL"  
description: "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list,"  
author: "AVADHESH PATEL"  
published: 2012-09-11  
updated: 2020-08-16  
canonical: https://www.mindstick.com/articles/999/subquery-with-max-function-sql  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# Subquery with MAX function SQL

## Subquery with MAX function SQL

Here, an aggregate may not appear in the [WHERE clause](https://www.mindstick.com/interview/1909/when-do-you-use-where-clause-and-when-do-you-use-having-clause) unless it is in a [subquery contained](https://www.mindstick.com/articles/1117/crud-operation-using-modal-dialog-in-asp-dot-net-mvc) in a [HAVING clause](https://www.mindstick.com/forum/33706/what-is-the-difference-between-having-clause-and-group-by-statement-in-sql-server) or a select list, and the column being aggregated is an outer [reference](https://www.mindstick.com/forum/34619/call-by-value-and-call-by-reference) in [SQL Server](https://www.mindstick.com/articles/34/create-table-in-microsoft-sql-server)\

For the demonstration, we have a table named ‘Info’ with some records.

```
--Select records from infoSELECT * FROM INFO
```

ScreenShot

![Subquery with MAX function SQL](https://www.mindstick.com/mindstickarticle/aa52cf33-90ce-4026-8b09-3b8d08824c39/images/61052cd6-50da-4915-a2c7-85e45346f697.png)

Problem Statement: Find all the details of INFO for the max id.

```
SELECT * FROM INFO WHERE ID = MAX(ID)
```

When he executed the above script it gave him the following error:

Message 147, Level 15, State 1, Line 3\
The aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

He was not able to [resolve this problem](https://answers.mindstick.com/qa/94690/why-does-it-take-google-chrome-so-long-to-open-in-my-computer-what-should-i-do-now-to-resolve-this-problem), even though the solution was given in the query description itself.

Due to a lack of [experience](https://www.mindstick.com/articles/12957/best-tips-to-make-your-ghostwriting-experience-a-success), he came up with another version of the above query based on the error message.

```
SELECT * FROM INFO HAVING ID = MAX(ID)
```

Message 8121, Level 16, State 1, Line 1

Column 'INFO.id' is invalid in the HAVING clause because it is not contained in either an aggregate function or the [GROUP BY clause](https://www.mindstick.com/forum/262/group-by-clause).

What he wanted actually was the table INFO [max value](https://www.mindstick.com/forum/159367/sql-to-calculate-number-of-values-for-each-day-based-on-a-max-value-from-previous-date) of ID. Based on the problem statement what the right solution is as following, which does not generate an error.

```
SELECT * FROM INFO WHERE ID = (SELECT MAX(ID) FROM INFO)
```

##### ScreenShot

![Subquery with MAX function SQL](https://www.mindstick.com/mindstickarticle/aa52cf33-90ce-4026-8b09-3b8d08824c39/images/298c94ec-5ad7-4c87-a56a-d848178bd990.png)

\

---

Original Source: https://www.mindstick.com/articles/999/subquery-with-max-function-sql

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.