【SQL】【MySQL】 現在日時に近い順にソートし、重複を排除する

お題

 * 現在日時に近い順にソートし、重複を排除する

サンプル

 * 顧客テーブル「customer」と予約テーブル「reservation」があり、
   予約日時に対して、現在日時に近い順にソートし、顧客名の重複を排除するデータを取得する

テーブル

customer
CREATE TABLE `customer` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`first_name` VARCHAR(50) NULL DEFAULT NULL,
	`family_name` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=7
;
reservation
CREATE TABLE `reservation` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`reservation_datetime` DATETIME NULL DEFAULT NULL,
	`customer_id` BIGINT(20) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

データ

customer
INSERT INTO `customer` (`first_name`, `family_name`) VALUES ('Mike', 'Abel');
INSERT INTO `customer` (`first_name`, `family_name`) VALUES ('Ken', 'Allen');
INSERT INTO `customer` (`first_name`, `family_name`) VALUES ('Kevin', 'Cox');
INSERT INTO `customer` (`first_name`, `family_name`) VALUES ('Smith', 'Abel');
INSERT INTO `customer` (`first_name`, `family_name`) VALUES ('Ken', 'Dean');
INSERT INTO `customer` (`first_name`, `family_name`) VALUES ('Tom', 'Abel');
reservation
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-04-01 11:00:00', 3);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-06-05 15:30:00', 2);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-07-22 12:30:00', 1);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-06-27 14:30:00', 4);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-06-29 12:30:00', 5);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-07-12 10:00:00', 2);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-08:31 12:30:00', 3);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-08-03 22:30:00', 6);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-08-13 12:30:00', 5);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-07-30 12:30:00', 3);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-06-25 12:30:00', 4);
INSERT INTO `reservation` (`reservation_datetime`, `customer_id`) VALUES ('2016-07-30 12:30:00', 2);

データ

"id"	"reservation_datetime"	"customer_id"	"first_name"	"family_name"
"1"	"2016-04-01 11:00:00"	"3"	"Kevin"	"Cox"
"2"	"2016-06-05 15:30:00"	"2"	"Ken"	"Allen"
"3"	"2016-07-22 12:30:00"	"1"	"Mike"	"Abel"
"4"	"2016-06-27 14:30:00"	"4"	"Smith"	"Abel"
"5"	"2016-06-29 12:30:00"	"5"	"Ken"	"Dean"
"6"	"2016-07-12 10:00:00"	"2"	"Ken"	"Allen"
"7"	"2016-08-31 12:30:00"	"3"	"Kevin"	"Cox"
"8"	"2016-08-03 22:30:00"	"6"	"Tom"	"Abel"
"9"	"2016-08-13 12:30:00"	"5"	"Ken"	"Dean"
"10"	"2016-07-30 12:30:00"	"3"	"Kevin"	"Cox"
"11"	"2016-06-25 12:30:00"	"4"	"Smith"	"Abel"
"12"	"2016-07-30 12:30:00"	"2"	"Ken"	"Allen"

SQL

SELECT
 c.first_name
FROM
 customer AS c
LEFT OUTER JOIN
 reservation AS r
ON
 c.id = r.customer_id
GROUP BY
 c.first_name
ORDER BY
 MIN(ABS(DATEDIFF(CURRENT_TIME, -- 「CURRENT_TIME」を可変にしてもいい
  CASE WHEN r.reservation_datetime IS NULL
   THEN '9999-12-31 23:59:59'
   ELSE r.reservation_datetime END))),
 MIN(ABS(TIMESTAMPDIFF(SECOND, CURRENT_TIME, -- 「CURRENT_TIME」を可変にしてもいい
  CASE WHEN r.reservation_datetime IS NULL
   THEN '9999-12-31 23:59:59'
   ELSE r.reservation_datetime END)))

出力結果

2016-08-03 23:02実行時の結果
"first_name"
"Tom"
"Ken"
"Kevin"
"Mike"
"Smith"


関連記事

MySQLで、 日付 / 日時 を扱うには...

http://blogs.yahoo.co.jp/dk521123/36279365.html