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
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.
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
it works! unbelievable, this would be the last thing I'd have suspected. All the more, it works without even without using the alias in the MAX(column) to reference the column. The more i learn, the more it seems I will never get there. Thank you very much Sebastian.
would it be too cheeky to ask why it wanted an alias? It's not like there is another FROM that could have caused a confusion, what difference it makes if i give it a name or not? I don't think it's compulsory to give alias to a subquery, or is it?
@Lana B It's a good question. When a subquery is used in a From clause SQL considers it a table and therefore requires a name. Think of a spreadsheet, the columns and filters don't have to have names, but the worksheet does. It's easy to forget (and a nuisance sometimes) but makes sense if you think about it that way.
@JJ32 thank you. I have taken this onboard and it already must have saved me time wondering why something fails, but knowing why helps. I have learnt so much on stackoverflow this week, thank you guys for your time.