December 3, 2014 at 5:50 pm
SELECT *
FROM
(
SELECT TOP (100) *
FROM tblAZ
ORDER BY ID DESC
)
ORDER BY ID ASC
Incorrect syntax near ')'.
December 3, 2014 at 5:54 pm
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)
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
December 3, 2014 at 6:01 pm
See post by AlexS. I'm trying to get LAST rows while preserving order.
December 3, 2014 at 6:06 pm
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
December 3, 2014 at 6:32 pm
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.
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
December 4, 2014 at 1:39 am
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
December 4, 2014 at 11:07 am
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.
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
December 4, 2014 at 11:28 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply