ORDER BY twice

[khiat]

Hi all
i have a problem in a task assigned to me in sql 2005

i have table contains column called ID which is an integer
i would to retrieve top 10 rows order by ID in descending order and i want the result of this query to be in ascending order .
when i tried the following query

SELECT * FROM
(
SELECT top 10 * FROM tableX ORDER BY ID DESC
) AS A
ORDER BY A.ID ASC

It gives error and when i searched about it i found that i cant to order by on the data retrieved from a sub query.
I hope any one can solve my problem??

[tesuji]

Hi

your select statement is formally quite correct, except for ID which is a well known system identifier of MS SQL server itself.

Because your select statement seems to be only constructed for asking your question it would really be a good idea to post the ORIGINAL erroneous statement AND the ORIGNAL error message too.

-- tesu

[Kumar203]

try like this....(Just remove AS before tablename A) you will get it.

SELECT * FROM
(
SELECT top 10 * FROM tableX ORDER BY ID DESC
) A
ORDER BY A.ID ASC

[tesuji]

try like this....(Just remove AS before tablename A) you will get it.

SELECT * FROM
(
SELECT top 10 * FROM tableX ORDER BY ID DESC
) A
ORDER BY A.ID ASC

Both (...) AS A and (...) A is SQL standard 1999/2003 and can't be responsible for his error, unfortunately he hasn't shown the exact select and message by now.

-- tesu