To obtain the state name associated with the maximum population

       
mysql>
mysql> CREATE TABLE states
    -> (
    ->  name            VARCHAR(30) NOT NULL,   # state name
    ->  abbrev          CHAR(2) NOT NULL,               # 2-char abbreviation
    ->  statehood       DATE,                                   # date of entry into the Union
    ->  pop                     BIGINT,                                 # population as of 4/1990
    ->  PRIMARY KEY (abbrev)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into states(name, abbrev, statehood, pop)values
    -> ("Alabama","AL","1819-12-14",4040587),
    -> ("Alaska","AK","1959-01-03",550043),
    -> ("Arizona","AZ","1912-02-14",3665228),
    -> ("Arkansas","AR","1836-6-15",2350725),
    -> ("California","CA","1850-9-9",29760021),
    -> ("Colorado","CO","1876-8-1",3294394),
    -> ("Connecticut","CT","1788-1-9",3287116),
    -> ("Delaware","DE","1787-12-7",666168),
    -> ("Florida","FL","1845-3-3",12937926),
    -> ("Georgia","GA","1788-1-2",6478216),
    -> ("Hawaii","HI","1959-08-21",1108229),
    -> ("Idaho","ID","1890-7-3",1006749),
    -> ("Illinois","IL","1818-12-3",11430602),
    -> ("Indiana","IN","1816-12-11",5544159),
    -> ("Iowa","IA","1846-12-28",2776755),
    -> ("Kansas","KS","1861-1-29",2477574),
    -> ("Kentucky","KY","1792-6-1",3685296),
    -> ("Louisiana","LA","1812-4-30",4219973),
    -> ("Maine","ME","1820-3-15",1227928),
    -> ("Maryland","MD","1788-4-28",4781468),
    -> ("Massachusetts","MA","1788-2-6",6016425),
    -> ("Michigan","MI","1837-1-26",9295297),
    -> ("Minnesota","MN","1858-5-11",4375099);
Query OK, 23 rows affected (0.00 sec)
Records: 23  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT SUBSTRING(MAX(CONCAT(LPAD(pop,8,' '),name)),9) FROM states;
+------------------------------------------------+
| SUBSTRING(MAX(CONCAT(LPAD(pop,8,' '),name)),9) |
+------------------------------------------------+
| California                                     |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> drop table states;
Query OK, 0 rows affected (0.00 sec)

Related examples in the same category

1.	Calculate the maximum value by using the MAX() function: MAX()
2.	Get the maximum price and minimum price
3.	Summarizing with MIN( ) and MAX( )
4.	What are the first and last state names, lexically speaking?
5.	Use the CONCAT( ) expression with MAX( ) to find the value with the largest population part:
6.	Finding Values Associated with Minimum and Maximum Values
7.	Determine the population range:
8.	Returning the Maximum Value with MAX()
9.	Finds the size of the largest message sent between each pair of sender and recipient values listed

To obtain the state name associated with the maximum population : MAX « Aggregate Functions « SQL / MySQL

Related examples in the same category