[thelist] MS SQL Syntax Help

[Judah McAuley]

Joshua Olson wrote:
> 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;

I don't see why that syntax should work anywhere since a select returns
a recordset, not a table.  I had a similar problem where I wanted to
create a full-text index on a view, but I couldn't because a view is a
recordset not a table.

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

In short, no I don't know that you can do that with SQL Server.  You can
use a SELECT to specify rows for an INSERT though...

Judah