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)

    This is the cute bit

    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.


  • If you needed to run more than one query against this you could simply build a temp table to hold the subquery data:

    This would make no difference if you are just running the one query, but if you wanted to run more than one against the same subset it would be much quicker.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!