1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Min(Case) is not giving me the minimum value

Discussion in 'SQL PL/SQL' started by uptothetop1, Mar 5, 2013.

  1. uptothetop1

    uptothetop1 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hello,

    I am just starting to teach myself some stuff about sql and writing queries to better analyze data. I have created the following data below and I am practicing some query writing when I began to have a problem with 'case' and 'min'.

    What I am trying to do is to look at each Continent then grab the smallest population value from a country in that Continent with an 'Emerging' status. This is column 'mine'.

    What I have noticed is that it is not giving me the minimum. For example South America is not giving me 30 from Chile but instead the 250 from Brazil. Can someone help me fix this. Thanks.

    h ttp://i.imgur.com/lys2twx.png?1

    please remove the space as I just joined I can't post links.
     
  2. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    Plz post the sql query. Its not much clear from the explanation, what exactly u've done.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    He did post the query -- had you looked at the png file he posted you'd have seen it.

    uptothetop1: Please post the create table statement you used to create the Continents table, as well as the statement or statements you used to load the data. Then provide the query in your post as it's not posslble to copy and paste from the provided png image.

    From the image, though, it appears the population numbers are stored as character strings, and if that is the case then '250' will sort out before '30' as the ASCII code for '2' is lower than the code for '3' and that's how the strings are evaluated.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    To follow up on my last comment:

    Code (SQL):
    SQL> CREATE TABLE continents(
      2          continent varchar2(60),
      3          country varchar2(60),
      4          STATUS  varchar2(25),
      5          population varchar2(12)
      6  );
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO continents
      3  VALUES('South America','Brazil','Emerging',250)
      4  INTO continents
      5  VALUES('South America','Chile','Emerging',30)
      6  SELECT * FROM dual;
     
    2 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT continent, MIN(population)
      2  FROM continents
      3  WHERE STATUS = 'Emerging'
      4  GROUP BY continent;
     
    CONTINENT                                                    MIN(POPULATI
    ------------------------------------------------------------ ------------
    South America                                                250
     
    SQL>
    SQL> DROP TABLE continents purge;
     
    TABLE dropped.
     
    SQL>
    SQL> CREATE TABLE continents(
      2          continent varchar2(60),
      3          country varchar2(60),
      4          STATUS  varchar2(25),
      5          population NUMBER
      6  );
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO continents
      3  VALUES('South America','Brazil','Emerging',250)
      4  INTO continents
      5  VALUES('South America','Chile','Emerging',30)
      6  SELECT * FROM dual;
     
    2 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT continent, MIN(population)
      2  FROM continents
      3  WHERE STATUS = 'Emerging'
      4  GROUP BY continent;
     
    CONTINENT                                                    MIN(POPULATION)
    ------------------------------------------------------------ ---------------
    South America                                                             30
     
    SQL>
     
    Bharat likes this.
  5. uptothetop1

    uptothetop1 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Yes, thank you. Once I changed the Population column I began to receive the correct results.