SQL/MySQL/Function/Compare Function

Passing Values to a Comparison Function

   <source lang="sql">

/* mysql> Drop table Transactions; Query OK, 0 rows affected (0.09 sec) mysql> CREATE TABLE Transactions (

   ->    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ->    OrderID INT NOT NULL,
   ->    DVDID SMALLINT NOT NULL,
   ->    DateOut DATE NOT NULL,
   ->    DateDue DATE NOT NULL,
   ->    DateIn DATE NOT NULL
   ->    )
   -> ENGINE=INNODB;

Query OK, 0 rows affected (0.22 sec) mysql> INSERT INTO Transactions (OrderID, DVDID, DateOut, DateDue)

   -> VALUES (1, 1, CURDATE(), CURDATE()+3),
   ->        (1, 4, CURDATE(), CURDATE()+1),
   ->        (1, 8, CURDATE(), CURDATE()+2),
   ->        (2, 3, CURDATE(), CURDATE()+3),
   ->        (3, 4, CURDATE(), CURDATE()+4),
   ->        (3, 1, CURDATE(), CURDATE()+5),
   ->        (3, 7, CURDATE(), CURDATE()+6),
   ->        (4, 4, CURDATE(), CURDATE()+7),
   ->        (5, 3, CURDATE(), CURDATE()+8),
   ->        (6, 2, CURDATE(), CURDATE()+7),
   ->        (6, 1, CURDATE(), CURDATE()+6),
   ->        (7, 4, CURDATE(), CURDATE()+6),
   ->        (8, 2, CURDATE(), CURDATE()+5),
   ->        (8, 1, CURDATE(), CURDATE()+4),
   ->        (8, 3, CURDATE(), CURDATE()+4),
   ->        (9, 7, CURDATE(), CURDATE()+3),
   ->        (9, 1, CURDATE(), CURDATE()+3),
   ->        (10, 5, CURDATE(), CURDATE()+1),
   ->        (11, 6, CURDATE(), CURDATE()+2),
   ->        (11, 2, CURDATE(), CURDATE()+6),
   ->        (11, 8, CURDATE(), CURDATE()+5),
   ->        (12, 5, CURDATE(), CURDATE()+4),
   ->        (13, 7, CURDATE(), CURDATE()+3);

Query OK, 23 rows affected (0.14 sec) Records: 23 Duplicates: 0 Warnings: 0 mysql> select * from Transactions; +----+---------+-------+------------+------------+------------+ | ID | OrderID | DVDID | DateOut | DateDue | DateIn | +----+---------+-------+------------+------------+------------+ | 1 | 1 | 1 | 2005-10-08 | 2005-10-11 | 0000-00-00 | | 2 | 1 | 4 | 2005-10-08 | 2005-10-09 | 0000-00-00 | | 3 | 1 | 8 | 2005-10-08 | 2005-10-10 | 0000-00-00 | | 4 | 2 | 3 | 2005-10-08 | 2005-10-11 | 0000-00-00 | | 5 | 3 | 4 | 2005-10-08 | 2005-10-12 | 0000-00-00 | | 6 | 3 | 1 | 2005-10-08 | 2005-10-13 | 0000-00-00 | | 7 | 3 | 7 | 2005-10-08 | 2005-10-14 | 0000-00-00 | | 8 | 4 | 4 | 2005-10-08 | 2005-10-15 | 0000-00-00 | | 9 | 5 | 3 | 2005-10-08 | 2005-10-16 | 0000-00-00 | | 10 | 6 | 2 | 2005-10-08 | 2005-10-15 | 0000-00-00 | | 11 | 6 | 1 | 2005-10-08 | 2005-10-14 | 0000-00-00 | | 12 | 7 | 4 | 2005-10-08 | 2005-10-14 | 0000-00-00 | | 13 | 8 | 2 | 2005-10-08 | 2005-10-13 | 0000-00-00 | | 14 | 8 | 1 | 2005-10-08 | 2005-10-12 | 0000-00-00 | | 15 | 8 | 3 | 2005-10-08 | 2005-10-12 | 0000-00-00 | | 16 | 9 | 7 | 2005-10-08 | 2005-10-11 | 0000-00-00 | | 17 | 9 | 1 | 2005-10-08 | 2005-10-11 | 0000-00-00 | | 18 | 10 | 5 | 2005-10-08 | 2005-10-09 | 0000-00-00 | | 19 | 11 | 6 | 2005-10-08 | 2005-10-10 | 0000-00-00 | | 20 | 11 | 2 | 2005-10-08 | 2005-10-14 | 0000-00-00 | | 21 | 11 | 8 | 2005-10-08 | 2005-10-13 | 0000-00-00 | | 22 | 12 | 5 | 2005-10-08 | 2005-10-12 | 0000-00-00 | | 23 | 13 | 7 | 2005-10-08 | 2005-10-11 | 0000-00-00 | +----+---------+-------+------------+------------+------------+ 23 rows in set (0.00 sec) mysql> SELECT OrderID, ID, DVDID

   -> FROM Transactions
   -> WHERE DVDID=LEAST(@dvd1, @dvd2)
   -> ORDER BY OrderID, ID;

Empty set (0.44 sec)

• /

Drop table Transactions; CREATE TABLE Transactions (

  ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  OrderID INT NOT NULL,
  DVDID SMALLINT NOT NULL,
  DateOut DATE NOT NULL,
  DateDue DATE NOT NULL,
  DateIn DATE NOT NULL
  )

ENGINE=INNODB; INSERT INTO Transactions (OrderID, DVDID, DateOut, DateDue) VALUES (1, 1, CURDATE(), CURDATE()+3),

      (1, 4, CURDATE(), CURDATE()+1),
      (1, 8, CURDATE(), CURDATE()+2),
      (2, 3, CURDATE(), CURDATE()+3),
      (3, 4, CURDATE(), CURDATE()+4),
      (3, 1, CURDATE(), CURDATE()+5),
      (3, 7, CURDATE(), CURDATE()+6),
      (4, 4, CURDATE(), CURDATE()+7),
      (5, 3, CURDATE(), CURDATE()+8),
      (6, 2, CURDATE(), CURDATE()+7),
      (6, 1, CURDATE(), CURDATE()+6),
      (7, 4, CURDATE(), CURDATE()+6),
      (8, 2, CURDATE(), CURDATE()+5),
      (8, 1, CURDATE(), CURDATE()+4),
      (8, 3, CURDATE(), CURDATE()+4),
      (9, 7, CURDATE(), CURDATE()+3),
      (9, 1, CURDATE(), CURDATE()+3),
      (10, 5, CURDATE(), CURDATE()+1),
      (11, 6, CURDATE(), CURDATE()+2),
      (11, 2, CURDATE(), CURDATE()+6),
      (11, 8, CURDATE(), CURDATE()+5),
      (12, 5, CURDATE(), CURDATE()+4),
      (13, 7, CURDATE(), CURDATE()+3);

select * from Transactions; SELECT OrderID, ID, DVDID FROM Transactions WHERE DVDID=LEAST(@dvd1, @dvd2) ORDER BY OrderID, ID;

      </source>