How do I find maximum in a column without using MAX function?

I was asked this question by a friend. You are given a table with just one field which is an integer. Can you the highest value in the field without using the MAX function ?

I think we can change the sign of each column and find the minimum using MIN function. Is that correct?

Answer

Why you would ignore using the function supported on any database is anyone’s guess, especially if you’d use the MIN function, but…

GREATEST

…some database vendors support the GREATEST function:

SELECT GREATEST(column)
  FROM ...

GREATEST returns the highest value, of all the columns specified. Those that support GREATEST include:

• MySQL
• Oracle
• PostgreSQL

TOP/LIMIT

TOP is SQL Server (2000+) only:

  SELECT TOP 1 column
    FROM YOUR_TABLE
ORDER BY column DESC

LIMIT is only supported by MySQL, PostgreSQL and SQLite

  SELECT column
    FROM YOUR_TABLE
ORDER BY column DESC
   LIMIT 1

ROW_NUMBER

ROW_NUMBER is supported by PostgreSQL 8.4+, Oracle 9i+, SQL Server 2005+:

SELECT x.col
  FROM (SELECT column AS col, 
               ROW_NUMBER() OVER (ORDER BY column DESC) AS rank
          FROM YOUR_TABLE) x
 WHERE x.rank = 1