Order by on an inline view

I would like to get the top 10 data from a table which needs to be sorted in ascending order in a outer query. Below is the pseudocode of the query. What are the options other than using table valued functions?

select * from (select top 10 tour_date from tourtable order by tour_date desc) order by tour_date a开发者_StackOverflow社区sc

Your query as written should work, you'd just need to alias the subquery:

select * 
    from (select top 10 tour_date from tourtable order by tour_date desc) t 
    order by tour_date asc 

Another alternative, assuming SQL Server 2005+:

SELECT t.tour_date
    FROM (SELECT tour_date, ROW_NUMBER() OVER(ORDER BY tour_date DESC) AS RowNum
              FROM tourtable) t
    WHERE t.RowNum <= 10
    ORDER BY t.tour_date ASC

which could also be written with a CTE:

WITH cteRowNum AS (
    SELECT tour_date, ROW_NUMBER() OVER(ORDER BY tour_date DESC) AS RowNum
        FROM tourtable
)
SELECT tour_date
    FROM cteRowNum
    WHERE RowNum <= 10
    ORDER BY tour_date ASC

Tested in a non-tsql context:

select * from (select tour_date from tourable order by tour_date desc limit 10) a order by tour_date asc