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

Question

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 1

Yes. You can do that as:

select MIN(-1 * col)*-1 as col from tableName;

Alternatively you can use the LIMIT clause if your database supports it.

One more alternative is to use a self-join of the table. Consider the query:

select A.col, B.col
from tableName as A, tableName as B
where A.col < B.col

It find all possible pairs and retains only those pairs where first field is less than second field. So your max value will not appear in the first field as it is not less that any other value.

You can make use of the above query as a subquery to select only the max value as:

select col from tableName where col not in
( select A.col from tableNAme as A, tableName as B
  where A.col < B.col)

If the table has multiple max values, the above query will return them all. To fix this you can use distinct in the outer select.