Difference between revisions of "Maximum"

From SQLZOO
Jump to: navigation, search
Line 12: Line 12:
 
</source>
 
</source>
 
<div>
 
<div>
<p>The function for using MAX over two fields is</p>
+
<p>The function for using <code>MAX</code> over two fields is</p>
 
  max(x,y) = (x + y + ABS(x-y))/2
 
  max(x,y) = (x + y + ABS(x-y))/2
<p>The function for using MIN over two fields is</p>
+
<p>The function for using <code>MIN</code> over two fields is</p>
 
  min(x,y) = (x + y - ABS(x-y))/2
 
  min(x,y) = (x + y - ABS(x-y))/2
 
</div>
 
</div>
 
<source lang='sql' class='def'>SELECT id, x, y, (x+y+ABS(x-y))/2  
 
<source lang='sql' class='def'>SELECT id, x, y, (x+y+ABS(x-y))/2  
 
FROM t</source>
 
FROM t</source>
<div class="ecomm e-mysql" style="display: none">In MySQL you can also use the GREATEST or LEAST functions to do this.</div>
+
<div class="ecomm e-mysql" style="display: none">In MySQL you can also use the <code>GREATEST</code> or <code>LEAST</code> functions to do this.</div>
<div class="ecomm e-oracle" style="display: none">In Oracle you can also use the GREATEST or LEAST functions to do this.</div>
+
<div class="ecomm e-oracle" style="display: none">In Oracle you can also use the <code>GREATEST</code> or <code>LEAST</code> functions to do this.</div>
  
 
{{Hacks Ref}}
 
{{Hacks Ref}}

Revision as of 14:59, 30 July 2012

Calculate the maximum or minimum of two fields

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
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense