order by twice

Question

order by twice

eng.khiat

SSC Enthusiast

Points: 144
More actions
June 29, 2010 at 3:24 am

#226264

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

Viewing 6 posts - 1 through 5 (of 5 total)

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

Try the following

; WITH cte_tableX AS
(
SELECTROW_NUMBER() OVER ( ORDER BY ID DESC ) RowNum, *
FROMtableX
)
SELECT*
FROMcte_tableX
WHERERowNum <= 10
ORDER BY RowNum DESC

Didnt test it though. But should work

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

eng.khiat SSC Enthusiast Points: 144 More actions · Answer 2

eng.khiat

SSC Enthusiast

Points: 144

June 29, 2010 at 3:51 am

#1186264

It works

Thanks man ... it was very helpful

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

eng.khiat (6/29/2010)
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??

The query works without error. You certainly can ORDER BY on the data retrieved from a subquery, providing you use TOP.

The inner query in the sample is generally known as a derived table, rather than a sub query.

DROP TABLE #tableX

CREATE TABLE #tableX ([ID] INT IDENTITY (1,1), MyStuff char(2))

INSERT INTO #tableX (MyStuff)

SELECT '01' UNION ALL

SELECT '02' UNION ALL

SELECT '03' UNION ALL

SELECT '04' UNION ALL

SELECT '05' UNION ALL

SELECT '06' UNION ALL

SELECT '07' UNION ALL

SELECT '08' UNION ALL

SELECT '09' UNION ALL

SELECT '10' UNION ALL

SELECT '11' UNION ALL

SELECT '12'

SELECT [ID], MyStuff

FROM (

SELECT TOP 10 *

FROM #tableX

ORDER BY ID DESC

) AS A

ORDER BY A.ID ASC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

Chris Morris-439714 (6/29/2010)
eng.khiat (6/29/2010)
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??
The query works without error. You certainly can ORDER BY on the data retrieved from a subquery, providing you use TOP.
The inner query in the sample is generally known as a derived table, rather than a sub query.

You are right Chris. It does work without error when i checked it with your sample data. No idea why the OP is getting an error.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Kingston Dhasian (6/29/2010)
No idea why the OP is getting an error.

Me neither Kingston but it's always nice to have an alternative.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden