I have some very basic SQL here:
SELECT TOP 1 *
FROM (SELECT TOP 8 *
FROM [BP_BasicPolicy ]
)
For some reason it does not compile and I get the error:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
What is the correct syntax? I don't understand why I'm getting this error as I thought it was perfectly legitimate SQL Server syntax
You need an alias for the subquery:
SELECT TOP 1 t.*
FROM (SELECT TOP 8 *
FROM [BP_BasicPolicy ]
) t
EDIT:
I hesitated to add, that using top without order by doesn't make sense. You need some ordering. So, you can do:
SELECT TOP 1 t.*
FROM (SELECT TOP 8 *
FROM [BP_BasicPolicy ]
ORDER BY col1 ASC
) t
ORDER BY col2 DESC;
Actually, you don't need the order by at the outermost level, but it is still a good idea.
Without the order by, you might as well do:
SELECT TOP 1 t.*
FROM [BP_BasicPolicy ] ;
This will choose an arbitrary row. For a specific one, use order by.
TOP without ORDER BY in the subquery.
ORDER BY without TOP, but this way around, it is of course just completely non-deterministic.
ORDER BY defines the order of results, sure, but in a subquery, ORDER BY is only useful to actually define the meaning of TOP (or for generating XML) so ORDER BY in a subquery without TOP or FOR XML generates an error.your query can be simplified as below, however if your intention isnt like that, please specify the objective, cheer :)
SELECT TOP 1 * FROM [BP_BasicPolicy ]
TOPwithoutORDER BYis a) meaningless, and b) not allowed in a subquery. What are you attempting to do?SELECT top 1 * FROM [BP_BasicPolicy] ORDER BY somethingwork?