SELECT from SELECT
By Kalman Toth, M.Phil., M.Phil., MCDBA

April 07, 2005

Normally we use table names in the FROM clause of a SELECT. Instead of table we can put a select query and think of it as a temporary table. This is the syntax:

FROM (SELECT…..) a,

where a is the alias.

The following example uses tables from the pubs sample database. It seeks which store sold the most of a particular title. The aliased SELECT is a group by.

select distinct a.title_id, b.stor_id, MaxSold from

(select title_id,MaxSold=max(qty) from sales

group by title_id ) a, sales b

where b.qty=a.MaxSold

To resolve the foreign keys we have to join in the stores and titles tables:

select distinct d.title, c.stor_name, MaxSold from

(select title_id,MaxSold=max(qty) from sales

group by title_id ) a, sales b, stores c, titles d

where b.qty=a.MaxSold

and b.stor_id=c.stor_id

and a.title_id=d.title_id

order by d.title, stor_name

This is the result:

But Is It User Friendly? Bookbeat 30

Computer Phobic AND Non-Phobic Individuals: Behavior Variations Doc-U-Mat: Quality Laundry and Books 20

Computer Phobic AND Non-Phobic Individuals: Behavior Variations News & Brews 20

Cooking with Computers: Surreptitious Balance Sheets Bookbeat 25

Cooking with Computers: Surreptitious Balance Sheets Doc-U-Mat: Quality Laundry and Books 25

Emotional Security: A New Algorithm Bookbeat 25

Emotional Security: A New Algorithm Doc-U-Mat: Quality Laundry and Books 25

Fifty Years in Buckingham Palace Kitchens Doc-U-Mat: Quality Laundry and Books 20

Fifty Years in Buckingham Palace Kitchens News & Brews 20

Is Anger the Enemy? Barnum's 75

Life Without Fear Bookbeat 25

Life Without Fear Doc-U-Mat: Quality Laundry and Books 25

Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean News & Brews 40

Prolonged Data Deprivation: Four Case Studies Bookbeat 15

Prolonged Data Deprivation: Four Case Studies Doc-U-Mat: Quality Laundry and Books 15

Prolonged Data Deprivation: Four Case Studies Fricative Bookshop 15

Secrets of Silicon Valley Barnum's 50

Silicon Valley Gastronomic Treats Bookbeat 10

Silicon Valley Gastronomic Treats Fricative Bookshop 10

Silicon Valley Gastronomic Treats News & Brews 10

Straight Talk About Computers Bookbeat 15

Straight Talk About Computers Doc-U-Mat: Quality Laundry and Books 15

Straight Talk About Computers Fricative Bookshop 15

Sushi, Anyone? Doc-U-Mat: Quality Laundry and Books 20

Sushi, Anyone? News & Brews 20

The Busy Executive's Database Guide Bookbeat 10

The Busy Executive's Database Guide Fricative Bookshop 10

The Busy Executive's Database Guide News & Brews 10

The Gourmet Microwave Bookbeat 25

The Gourmet Microwave Doc-U-Mat: Quality Laundry and Books 25

You Can Combat Computer Stress! Fricative Bookshop 35

One more note. The pubs database uses the old-fashioned naming with underscores. Stor_id and stor_name is most unfortunate naming, because you have to remember to drop the ‘e’. In the Hungarian notation, the proper naming would be StoreID and StoreName .