Can a subquery be used as a table in the FROM clause?

[Dan01]

I have a query that sorts records in descending order where
fielda fieldb
100 9911300704
100 9911300703
100 9911300702
I would like to use that query to create a temporary table that can be used in the FROM clause of another query, which does a DISTINCT fieldA to select only the first first record. Does the temp table strategy make sense? If so, how would one do that? Thanks, Dan.

 

[tlbroadbent]

You can use a sub-query as a source in a FROM clause.

Example:

Select t.Col1, t.Col2, q.ColY, q.ColZ
From table1 As t
Inner Join (Select ColX, ColY, ColZ From table2) As q
On t.Col1=q.ColX
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Dan01]

Thanks Terry. Can table1 and table2 be actually the same table? Dan.

 

[tlbroadbent]

Yes. Of course, these answers depend on which RDMS you use. Not all support sub-queries. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Dan01]

Hi Terry, does Access support it? Dan.

 

[tlbroadbent]

Dan,

Yes, Access can handle sub-queries. I recommend the Access Queries and Access/SQL forum (forum701) for you Access questions. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.