3 Replies Latest reply: Aug 16, 2014 3:42 PM by Rahul_India RSS

    Subquery Help

    pallis
      name         continent gdp

      Afghanistan   Asia      10000

      Albanis      Europe200000

      France        Europe    230000

      China         Asia      400000

      New York      USA       450000

       

      Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

       

      select name from world a where gdp in (

      select * from world b where b.continent='Europe' and a.gdp>= b.gdp)

       

      Please correct my query.My query is giving wrong results

        • 1. Re: Subquery Help
          AquaNX4

          Hello Pallis:

           

          We can help you but your question is not that clear.

           

          Post a sample output of what you are looking for.

           

          Aqua

          • 2. Re: Subquery Help
            Etbin

            Maybe


            with

            t as

            (select 'Afghanistan' name,'Asia' continent,10000 gdp from dual union all

            select 'Albanis','Europe',200000 from dual union all

            select 'France','Europe',230000 from dual union all

            select 'China','Asia',400000 from dual union all

            select 'New York','USA',450000 from dual

            )

            select name

              from t

            where gdp > (select max(gdp)

                            from t

                           where continent = 'Europe'

                         )

             

            NAME
            China
            New York

             

            Regards

             

            Etbin

            • 3. Re: Re: Subquery Help
              Rahul_India

              Another way

               

               

              with
              t as
              (select 'Afghanistan' name,'Asia' continent,10000 gdp from dual union all
              select 'Albanis','Europe',200000 from dual union all
              select 'France','Europe',230000 from dual union all
              select 'China','Asia',400000 from dual union all
              select 'New York','USA',450000 from dual
              )
              select a.* from t a where  a.continent !='Europe' and a.gdp > all ( select b.gdp from t b where  b.continent ='Europe');