Maximum

From SQLZoo
Jump to navigation Jump to search

In this example you are shows how to use the MAX or MIN functions to find the maximum or minimum over two fields instead of just one.

schema:scott
DROP TABLE t
 CREATE TABLE t(
  id VARCHAR(10),
  x INTEGER,
  y INTEGER );
INSERT INTO t VALUES ('A',1,2);
INSERT INTO t VALUES ('B',4,3);
INSERT INTO t VALUES ('C',5,5);

The function for using MAX over two fields is

max(x,y) = (x + y + ABS(x-y))/2

The function for using MIN over two fields is

min(x,y) = (x + y - ABS(x-y))/2
SELECT id, x, y, (x+y+ABS(x-y))/2 
  FROM t
DataWars, Data Science Practice Projects - LogoDataWars: Practice Data Science/Analysis with +100 Real Life Projects