To find the row with the most recent birth date, the query is similar, except that you sort in descending orde : Date « Data Type « SQL / MySQL






To find the row with the most recent birth date, the query is similar, except that you sort in descending orde

    
r
mysql>
mysql> CREATE TABLE mytable
    -> (
    ->  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->  name    CHAR(20) NOT NULL,
    ->  birth   DATE,
    ->  color   ENUM('blue','red','green','brown','black','white'),
    ->  foods   SET('lutefisk','burrito','curry','eggroll','fadge','pizza'),
    ->  cats    INT,
    ->  PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO mytable
    ->  VALUES
    ->          (NULL,'Jack','1970-04-13','black','eggroll,pizza,fadge',0),
    ->          (NULL,'Tom','1969-09-30','white','curry,eggroll,burrito',3),
    ->          (NULL,'Mary','1957-12-01','red','burrito,pizza,curry',1),
    ->          (NULL,'Jane','1973-11-02','red','pizza,eggroll',4),
    ->          (NULL,'Sean','1963-07-04','blue','burrito,curry',5),
    ->          (NULL,'Alan','1965-02-14','red',',curry,eggroll',1),
    ->          (NULL,'March','1968-09-17','green','fadge,lutefisk',1),
    ->          (NULL,'Shane','1975-09-02','black','pizza,curry',2),
    ->          (NULL,'Dan','1952-08-20','green','fadge,lutefisk',0),
    ->          (NULL,'Tony','1960-05-01','white','pizza,burrito',0)
    -> ;
Query OK, 10 rows affected, 1 warning (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 1

mysql>
mysql> SELECT * FROM mytable;
+----+-------+------------+-------+-----------------------+------+
| id | name  | birth      | color | foods                 | cats |
+----+-------+------------+-------+-----------------------+------+
|  1 | Jack  | 1970-04-13 | black | eggroll,fadge,pizza   |    0 |
|  2 | Tom   | 1969-09-30 | white | burrito,curry,eggroll |    3 |
|  3 | Mary  | 1957-12-01 | red   | burrito,curry,pizza   |    1 |
|  4 | Jane  | 1973-11-02 | red   | eggroll,pizza         |    4 |
|  5 | Sean  | 1963-07-04 | blue  | burrito,curry         |    5 |
|  6 | Alan  | 1965-02-14 | red   | curry,eggroll         |    1 |
|  7 | March | 1968-09-17 | green | lutefisk,fadge        |    1 |
|  8 | Shane | 1975-09-02 | black | curry,pizza           |    2 |
|  9 | Dan   | 1952-08-20 | green | lutefisk,fadge        |    0 |
| 10 | Tony  | 1960-05-01 | white | burrito,pizza         |    0 |
+----+-------+------------+-------+-----------------------+------+
10 rows in set (0.00 sec)

mysql>
mysql> SELECT * FROM mytable ORDER BY birth DESC LIMIT 1;
+----+-------+------------+-------+-------------+------+
| id | name  | birth      | color | foods       | cats |
+----+-------+------------+-------+-------------+------+
|  8 | Shane | 1975-09-02 | black | curry,pizza |    2 |
+----+-------+------------+-------+-------------+------+
1 row in set (0.00 sec)

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

   
    
    
    
  








Related examples in the same category

1.Date type value in the form '2003-12-31', range 1000-01-01 to 9999-12-31 (3 bytes)
2.Compare with Date value
3.How MySQL deals with incorrect date value
4.Date literal in where clause
5.Date value literal
6.Date type value inside in operator and subquery
7.Date calculation with variable
8.Date default value
9.Sorting by Calendar Day