> > > > > SELECT * FROM ( SELECT * FROM T1 ) T2;
> > > >
> > > > interesting - which SQL servers understand this SQL and what
> > > does it mean
> > > in
> > > > plain language?
> > >
> > > MS SQL does.
> > >
> > so, what does it mean?
>
> You are selecting from the resultset in the inner select. I have no idea
> why though :-) ...

Oracle has them too, and that is sometimes quite useful,
especially during development.

For example you have a SELECT with 20 fields and want to know
(for test purposes) the number of records returned or if you
want to use any aggregate functions, then you could do something
like that:

SELECT COUNT(*) FROM (
SELECT .... HUGE_FIELD_LIST ... FROM .....
)

or

SELECT AVG(FIELD) FROM (
SELECT .... HUGE_FIELD_LIST ... FROM .....
)

If you want to do that now, you have to rearrange/change the
SELECT statement, for example replacing the field list with
the aggregate function, and also adding the GROUP BY.


For example using TOAD with Oracle you can do something like
that:

SELECT AVG(FIELD) FROM (

SELECT .... HUGE_FIELD_LIST ... FROM ..... <= cursor here

)

Position the cursor at the line of the inner select, and
execute the SELECT statement. If you want to know how
many records the inner-select returns, then you don't
have to change the inner-select, but rather remove the
empty lines before and after the inner-select, so that
it will be:

SELECT COUNT(FIELD) FROM (
SELECT .... HUGE_FIELD_LIST ... FROM ..... <= cursor here
)

Execute this statement and you know how many records
are returned from the inner-select.

Right, perhaps the example is somewhat CONSTRUCTED *g*, but
in some situations derived tables are useful. ;-)


Best Regards,
Thomas Steinmaurer

Logging/Auditing Suite for InterBase and Firebird
http://www.iblogmanager.com
Logging/Auditing Suite for Advantage Database Server
http://www.adslogmanager.com