CODEWITHSUNDEEP
sqlsql-server
kqlambert
kqlambert

Reputation: 2711

SQL Server reverse order after using desc

I want to reverse the order in SQL Server of results after using desc. For example:

SELECT TOP 3 * FROM table ORDER BY id DESC

returns results:

505
504
503

But then I want to flip the results to look like this:

503
504
505

I tried

SELECT * FROM (SELECT TOP 3 * FROM table ORDER BY id DESC) ORDER BY id ASC

But that did not work. How can I fix it?

Upvotes: 23

Views: 41635

Answers (6)

kqlambert
kqlambert

Reputation: 2711

SELECT * FROM (SELECT TOP 3 * FROM table ORDER BY id DESC) AS r ORDER BY r.id ASC

I figured it out. I needed to make a temporary table and have a name using AS.

Upvotes: 1

Limitless isa
Limitless isa

Reputation: 3802

SELECT *
FROM (
    SELECT *
    FROM table
    ORDER BY ID DESC
) TMP
ORDER BY TMP.ID ASC

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

;WITH cte
AS
(
   SELECT *, ROW_NUMBER() OVER(ORDER BY id DESC) rank
   FROM table
)
SELECT * 
FROM cte
WHERE rank <= 3 
ORDER BY id ASC

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

I think you forgot the subselect alias

SELECT * 
FROM (
    SELECT TOP 3 * 
    FROM table 
    ORDER BY id DESC
) s
ORDER BY id ASC

Upvotes: 7

George Mastros
George Mastros

Reputation: 24498

SELECT * FROM (SELECT TOP 3 * FROM table ORDER BY id DESC) As AliasName ORDER BY id ASC

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

That should work as long as you alias the subquery.

SELECT q.* 
    FROM (SELECT TOP 3 * 
              FROM table 
              ORDER BY id DESC) q
    ORDER BY q.id ASC

Upvotes: 28

Related Questions