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);
精彩评论