[thelist] MS SQL Syntax Help

[Joshua Olson]

I've seen queries such as this in a few different database examples:

SELECT *
FROM   (SELECT * FROM my_table ORDER BY col_name_1 DESC)
WHERE  ROWNUM < 10;

This example works in Oracle, as far as I know, to return the top 10 rows in
a table..  In MS SQL 2k, however, the parser does not seem to like having
the inner select within the FROM clause.  I understand that there are
usually other ways to accomplish a task not involving this syntax, but I'm
still curious.  Does anyone know a trick to use SELECT's in the FROM clause
in MS SQL 2k?

TIA,

-joshua

ref: http://www.orafaq.com/faqsql.htm#TOP