Results 1 to 4 of 4

Thread: Inner and outer select

  1. #1
    Join Date
    Feb 2010
    Posts
    5

    Inner and outer select

    In my SQL learning I have reached to the following question:

    Find the largest country in each region
    The table bbc has (name, region, population)

    The answer is

    SELECT region, name, population FROM bbc x
    WHERE population >= ALL
    (SELECT population FROM bbc y
    WHERE y.region=x.region
    AND population>0)

    I couldn't understand it and need an explanation guys

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Is it for sql server?

  3. #3
    Join Date
    Feb 2010
    Posts
    5
    Yes it is for SQL server

  4. #4
    Join Date
    Mar 2010
    Posts
    7

    Max

    The ALL keyword does a set comparison on the values of one column. Essentially, it validates and identifies the row with the largest value in that column.

    Regarding your query, I assume that the name column is an appellation for country. If that is true, your query is equivalent to the following query:

    SELECT region, name, max(population)
    FROM bbc
    WHERE population > 0
    GROUP BY region, name

    The purpose of the MAX and ALL keywords are very similar. Most all populations you can create using ALL , you can create with using MAX. I suppose a key difference is that MAX will return <=1 row per group, while ALL will/can return 0 or >=1 rows per group (for example, suppose one region had two countries with the same population.)

    I find use for the SOME/ANY keyword. I do not use ALL.

    I always appreciate additional insight.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •