SQL: Nested SELECT statements

Here is an example of NESTING one SQL query within another
in order to return a subset of data that could not be done within normal SELECT parameters....

Here's the scenario I had,

I have two tables grup and gchg

grup contains the ID & Description Fields I want
gchg contains many records for each grup.ID - I want just the
LATEST record for each Grup.ID (specified by the latest effective date)

So, we need to find the maximum effective date in the gchg table for each ID.

The tables can be linked - 1 to many.

BTW - reason, there are 41,000 records in the gchg table... to import this into XL
would be suicidal, when I only need the 80 latest records for a simple VLOOKUP

OK, Here's the SQL (This is from SQL 2000 Query Analyser)

SELECT
	gchg.id, 
	grup.desc_,
	gchg.effective, 
	grup.type
FROM
    	gchg,grup
WHERE 	grup.rowno = gchg.rowno_grupgchg_grup  
AND 	grup.type = 'revenue'
AND 	grup.status = 'active'
AND	gchg.effective = (
		SELECT MAX(gchg_alias.effective) 
		FROM gchg AS gchg_alias
		WHERE gchg_alias.id = gchg.id)
ORDER BY
    	gchg.id ASC

This is the cute bit

WHERE....
AND	gchg.effective = (
	SELECT MAX(gchg_alias.effective) 
	FROM gchg AS gchg_alias
	WHERE gchg_alias.id = gchg.id)

We set an alias up of the gchg table in our nested SELECT
statement and link it to gchg using the ID field of both

FROM gchg AS gchg_alias 'names the alias table

WHERE gchg_alias.id = gchg.id ' links them together

SELECT MAX(gchg_alias.effective) 'gets the Max of each groupIDs effective date

Using a further select query as part of the WHERE clause
saves us having to do multiple separate queries to pare down are data
to manageable subsets.....

In my case, It meant a very easy way of importing 80 records as opposed to 41,000

Hope the structure helps someone else.

Will