Warm tip: This article is reproduced from stackoverflow.com, please click
sql-server tsql

SQL Server subquery with top and max returning error

发布于 2020-03-29 21:01:43

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.

Questioner
Lana B
Viewed
25
Sebastian Brosch 2020-01-31 18:57

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