Mega Code Archive
To find the earliest birthday within the calendar year, sort by the month and day of the birth values
<![CDATA[
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>
mysql> SELECT name, DATE_FORMAT(birth,'%m-%e') AS birthday FROM mytable ORDER BY birthday LIMIT 1;
+------+----------+
| name | birthday |
+------+----------+
| Alan | 02-14 |
+------+----------+
1 row in set (0.00 sec)
mysql>
mysql> drop table mytable;
Query OK, 0 rows affected (0.00 sec)
mysql>
]]>