CODEWITHSUNDEEP
sql-servert-sql
Lana B
Lana B

Reputation: 496

SQL Server subquery with top and max returning error

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

Answers (2)

Amit Kumar
Amit Kumar

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

Sebastian Brosch
Sebastian Brosch

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

Related Questions