The sign function, yes the function that returns 1,0, or -1 for values greater than 1, 0 and less than zero has not been given the credit it deserves
sign(4)=1
sign(0)=0
sign(-12)=-1
I know, you think it's a silly useless function. Well, it can define abs(). Yes, the absolute value can be defined as the sign() of a number times itself, or the following.
sign(x) (x) = abs(x)
Do you see what I mean? See how powerful it is? We can now define condition statements, find maximum values, and do max min comparison. Hmm...you still don't believe me? Here, see for yourself.
1 - abs(sign(x-y)) is equal to 0 if x > y
1 - abs(sign(x-y)) is equal to 1 if x = y
1 - abs(sign(x-y)) is equl to 0 if x < y
So with the above expression, you will get a 1 if x is equal to y. For shorthand Delta[x=y] can be replaced with 1 - abs(sign(x-y)). Now note that all the other Delta's can be defined as follows.
Delta[x=y] = 1 - abs(sign(x-y))
Delta[x!=y] = abs(sign(x-y))
Delta[x<y] = 1-sign(1+sign(x-y))
Delta[x<=y] = sign(1-sign(x-y))
Delta[x>y] = 1-sign(1-sign(x-y))
Delta[x>=y] = sign(1+sign(x-y))
And, it's possible to return the max or min value. And you don't need no stinking temperary variable.
Max(x,y) = x + (y - x)* Delta[x<=y]
or
Max(x,y) = x + (y - x)* ( 1-sign(1+sign(x-y)) )
or with x=5 and y=4
Max(5,4) = 5 + (4 - 5)* ( 1 - sign(1+sign(5-4)) )
Max(5,4) = 5 + (4 - 5)* ( 1 - sign(1 + 1))
Max(5,4) = 5 + (4 - 5)* ( 1 - 1 )
Max(5,4) = 5 + (4 - 5)* ( 0 )
Max(5,4) = 5
Ok, So What
It's not just a cute math problem. This can be used to solve complex SQL queries in a single pass because these Delta conditions can be done before the where expression or in the having expression.
Here's a quick example done in MySQL.
CREATE TABLE exams (
pkey int(11) NOT NULL auto_increment,
name varchar(15),
exam int,
score int,
PRIMARY KEY (pkey)
);
insert into exams (name,exam,score) values ('Bob',1,75);
insert into exams (name,exam,score) values ('Bob',2,77);
insert into exams (name,exam,score) values ('Bob',3,78);
insert into exams (name,exam,score) values ('Bob',4,80);
insert into exams (name,exam,score) values ('Sue',1,90);
insert into exams (name,exam,score) values ('Sue',2,97);
insert into exams (name,exam,score) values ('Sue',3,98);
insert into exams (name,exam,score) values ('Sue',4,99);
mysql> select * from exams;
+------+------+------+-------+
| pkey | name | exam | score |
+------+------+------+-------+
| 1 | Bob | 1 | 75 |
| 2 | Bob | 2 | 77 |
| 3 | Bob | 3 | 78 |
| 4 | Bob | 4 | 80 |
| 5 | Sue | 1 | 90 |
| 6 | Sue | 2 | 97 |
| 7 | Sue | 3 | 98 |
| 8 | Sue | 4 | 99 |
+------+------+------+-------+
8 rows in set (0.00 sec)
mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from exams group by name;
+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob | 75 | 77 | 78 | 80 |
| Sue | 90 | 97 | 98 | 99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)
Note, the above pivot table was created with one select statement
You may think IF's would work. And that should be true, but it is not the case for MySQL 4.1 There is a bug in that version of MySQL.
SQLite, my favorite database, does not natively support the sign function. But, you can easily create it. Anyway, if you're looking for more examples, then, you might want to check here [See LONGWINDED TIPS]. These examples only scratch the surface of what can be done.
