Reputation: 496
Why doesn't this code work? I get this error
Error near ')'
on the last line. I cannot see where I made a syntax error (this is for SQL Server 2017).
DECLARE @NumRows INT;
SELECT @NumRows = COUNT(*) / 2
FROM SAMA;
SELECT MAX(NoMonths)
FROM
(SELECT TOP(@NumRows) NoMonths
FROM SAMA
ORDER BY NoMonths ASC)
What the query is trying to do, is to find a max of top N rows, and the N is defined in the variable.
Without MAX
, the subquery works and returns N rows. But when I add MAX
, it fails.
Upvotes: 0
Views: 57
Reputation: 187
You need to have a table alias
DECLARE @NumRows INT;
SELECT @NumRows = COUNT(*) / 2 FROM SAMA;
SELECT MAX(NoMonths)
FROM (SELECT TOP(@NumRows) NoMonths FROM SAMA ORDER BY NoMonths ASC) sama_alias
Upvotes: 2
Reputation: 43604
You have to set a table alias:
DECLARE @NumRows INT;
SELECT @NumRows = COUNT(*) / 2 FROM SAMA;
SELECT MAX(NoMonths)
FROM (
SELECT TOP(@NumRows) NoMonths
FROM SAMA
ORDER BY NoMonths ASC
) table_alias
Upvotes: 2