Subquery incorrect syntax error

Question

Post reply

Subquery incorrect syntax error

Alex Jordan

SSC Eights!

Points: 880
More actions
December 3, 2014 at 5:50 pm

#301590

SELECT *
FROM
(
SELECT TOP (100) *
FROM tblAZ
ORDER BY ID DESC
)
ORDER BY ID ASC
Incorrect syntax near ')'.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Drop the ORDER BY, can't order subqueries.

Also, alias the subquery. IE:

SELECT *

FROM (

SELECT * FROM tblaz) AS drv

ORDER BY

drv.column1

EDIT: Also, you want SELECT TOP 100, not SELECT TOP (100)

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Alex Jordan SSC Eights! Points: 880 More actions · Answer 2

See post by AlexS. I'm trying to get LAST rows while preserving order.

https://ask.sqlservercentral.com/questions/2013/how-to-select-bottom-n-rows-in-a-table-through-sin.html

Alex Jordan SSC Eights! Points: 880 More actions · Answer 3

I figured it out. Apparently I can order sub-queries as long as I provide a alias.

Well this works:

SELECT *

FROM

(

SELECT TOP 100 *

FROM tblAZ

ORDER BY ID DESC

) AS drv

ORDER BY drv.ID ASC

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

No, you can order subqueries as long as you're looking for a TOP statement. My apologies, I read that as TOP 100%. My bad.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Alex Jordan (12/3/2014)
I figured it out. Apparently I can order sub-queries as long as I provide a alias.

Nothing to do with the ORDER. Derived tables always need an alias.

Edit, and you do want those brackets around the number in the TOP. TOP (100).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

GilaMonster (12/4/2014)
Alex Jordan (12/3/2014)
I figured it out. Apparently I can order sub-queries as long as I provide a alias.
Nothing to do with the ORDER. Derived tables always need an alias.
Edit, and you do want those brackets around the number in the TOP. TOP (100).

Really? I've never used them. Hrm, need to modify my scripting.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

Evil Kraig F (12/4/2014)
GilaMonster (12/4/2014)
Alex Jordan (12/3/2014)
I figured it out. Apparently I can order sub-queries as long as I provide a alias.
Nothing to do with the ORDER. Derived tables always need an alias.
Edit, and you do want those brackets around the number in the TOP. TOP (100).
Really? I've never used them. Hrm, need to modify my scripting.

Yes, you do. From BOL.

Compatibility Support
For backward compatibility, the parentheses are optional in SELECT statements. We recommend that you always use parentheses for TOP in SELECT statements for consistency with its required use in INSERT, UPDATE, MERGE, and DELETE statements in which the parentheses are required.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2