articles

Home / DeveloperSection / Articles / Subquery with MAX function SQL

Subquery with MAX function SQL

Subquery with MAX function SQL

AVADHESH PATEL 24959 11-Sep-2012

Subquery with MAX function SQL

Here, an 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 in SQL Server

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

--Select records from info
SELECT * FROM INFO

 ScreenShot

Subquery with MAX function SQL

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, even though the solution was given in the query description itself.

Due to a lack of experience, 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.

What he wanted actually was the table INFO max value 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



Updated 16-Aug-2020
Avadhesh Kumar Patel District Project Manager - Aligarh 14 months work experience in Panchayati Raj Department Sector as District Project Manager & 12 months work experience in IT Sector as Software Engineer. :-)

Leave Comment

Comments

Liked By