Everybody was a beginner once and I always like to get involved in the questions from beginners. There is a big difference between the question for beginner and question from advanced user. I have noticed that if an advanced user gets an error, they usually need just a small hint to resolve the problem. Let us learn about how to fix Error 147 in this blog post.
However, when a beginner gets error he sometimes sits on the error for a long time as he/she has no idea about how to solve the problem as well have no idea regarding what is the capability of the product. I recently received a very novice level question. When I received the problem I quickly see how the user was stuck. When I replied him with the solution, he wrote a long email explaining how he was not able to solve the problem. He thanked multiple times in the email. This whole thing inspired me to write this quick blog post.
I have modified the user’s question to match the code with AdventureWorks as well simplified so it contains the core content which I wanted to discuss.
Problem Statement: Find all the details of SalesOrderHeaders for the latest ShipDate.
He comes up with following T-SQL Query:
SELECT * FROM [Sales].[SalesOrderHeader] WHERE ShipDate = MAX(ShipDate) GO
When he executed above script, it gave him following error:
Msg 147, Level 15, State 1, Line 3
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.
He was not able to resolve this problem, even though the solution was given in the query description itself. Due to lack of experience, he came up with another version of above query based on the error message.
SELECT * FROM [Sales].[SalesOrderHeader] HAVING ShipDate = MAX(ShipDate) GO
When he ran above query it produced another error.
Msg 8121, Level 16, State 1, Line 3
Column ‘Sales.SalesOrderHeader.ShipDate’ is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
What he actually wanted was the SalesOrderHeader all the Sales shipped on the last day. Based on the problem statement what the right solution is as following, which does not generate error.
SELECT * FROM [Sales].[SalesOrderHeader] WHERE ShipDate = (SELECT MAX(ShipDate) FROM [Sales].[SalesOrderHeader])
Well, that’s it! Very simple. With SQL Server there are always multiple solution to a single problem. Are there any other solution available to the problem stated? Please share in the comment.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
15 Comments. Leave new
How about using a Common TablenExpression, instead of having the inline sub query?
** Common Table Expression. Sorry for the spelling mistake..
select top (1) with ties * from [Sales].[SalesOrderHeader] order by ShipDate desc
Nice alternative, especially if I have to sort by “Ship Date” once
I would say this is the right way to do it. Not to forget that MAX function will have a significant impact on the overall performance (in the absence of a nolock it has the potential of locking the table as well – ofc this is only when the table contains large amount of data).
Is there any performance issue if the data is growth in SalesOrderHeader, because we are using same table reference (SalesOrderHeader)
Hello pinal,
its very nice post.
There is one another way to do this.
WITH Max_Ship_Date AS (
SELECT Date_Shipped = MAX(ShipDate)
FROM SalesOrderHeader
)
SELECT *
FROM SalesOrderHeader
INNER JOIN Max_Ship_Date ON
SalesOrderHeader.ShipDate = Max_Ship_Date.Date_Shipped
There are multiple ways to do the same thing in SQL. One of the things one could do is instead of thinking in syntactical fashion is to start with the data and see where one needs to get to that could help one to get different solutions.
Hi pinal sir,
Alternative to above solution :
SELECT TOP 1 * FROM [Sales].[SalesOrderHeader] ORDER BY ShipDate DESC
Hi Sir,
SELECT TOP 1 * FROM [Sales].[SalesOrderHeader] ORDER BY ShipDate DESC
I Think this is the best way in performance wise..
with cte as
(select row_number over(order by ShipDate DESC)ID,* from [Sales].[SalesOrderHeader])
Select * from cte where id=1
thanks boss it work from me in this my case
Amazin!!!, i was stcke for the same mistake, so helpful!!!!
@Pinal Dave – Getting a solution is great. But, it would be better if you could also include the reasons for the errors. Once one knows the reasons, one will not make the same mistake. Otherwise, one will have to memorize this and it can bite you in SQL interviews.