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

My friend asked this question to me -

You are given a table with just one field which is an integer. Can you get the highest value in the field without using the MAX function ?

I think we can change开发者_C百科 the sign of each column and find the minimum using MIN function. Is that correct?

---

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.

---

Let's pile on with more ways to do it!

SELECT DISTINCT t1.col
FROM tableName t1
  LEFT JOIN tableName t2
    ON t2.col > t1.col AND t2.col IS NOT NULL
WHERE t2.col IS NULL

---

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

---

Yes, you could negate everything and then use min.

That would, of course, end up giving you the negation of the value you want, which you would then have to negate again to get the actual value.

Of course, other than academic interest, I'd have a hard time believing you'd find a DBMS with a min function and no max.

You could also order by that column descending and then just extract the first row, something like:

select my_column from my_table
order by my_column desc
fetch first row only;

---

use order by number desc limit 1

---

This query below could also get you the maximum for a specific column without using the MAX function.

select top 1 (col) from tablename order by col desc

---

for getting the highest value in column

Select cl1, cl2 from t1 where cl2 >=all( select cl2from t1);