Subquery incorrect syntax error

  • SELECT *

    FROM

    (

    SELECT TOP (100) *

    FROM tblAZ

    ORDER BY ID DESC

    )

    ORDER BY ID ASC

    Incorrect syntax near ')'.

  • 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

  • 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

  • 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

  • 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

  • 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
  • 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

  • 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

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

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