CODEWITHSUNDEEP
sqlsql-server
Ben
Ben

Reputation: 2725

SQL Server syntax issue

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

Upvotes: 0

Views: 89

Answers (2)

Low Chee Mun
Low Chee Mun

Reputation: 610

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 ]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 6

Related Questions