Shulou(Shulou.com)05/31 Report--

This article introduces you how to use greatest function and least function in oracle, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Greatest (max (one), max (two), max (three))

Finding the maximum of multiple columns, the greatest function in oracle

The data of the known table TB is as follows

SQL > select * from tb; ID CHINESE MATH ENGLISH-1001 89 98 87 1002 81 87 79

Now to get the following results, how to solve the problem

ID CHINESE MATH ENGLISH MAX MIN-1001 89 98 87 98 87 1002 81 87 79 87 79

After thinking about it for a long time, I didn't think of any good way. First of all, I naturally thought of using MAX and MIN functions, but obviously these two are aggregate functions, which are intended to act on a Group of the same column, but now the values of MAX and MIN act on each row. If you want to use MAX () and MIN (), you also need to process the data structure of the original table (unpivot first). But obviously not so good.

See a netizen reply with greatest and least function to do, really concise and beautiful, but also for their own ignorance and sweat ah

The solution is as follows

SQL > SELECT id, chinese, math, english, 2 greatest (chinese, math, english) max, 3 least (chinese, math, english) min 4 FROM tb ID CHINESE MATH ENGLISH MAX MIN-1001 89 98 87 98 87 1002 81 87 79 87 79

The use of least is the same

Grammar introduction:

1 Grammar

GREATEST (expr_1, expr_2,... expr_n)

2 description

GREATEST (expr_1, expr_2,... expr_n) functions expr_1 from expressions (columns, constants, calculated values)

Expr_2,... Expr_n, etc., to find the maximum number to return. When comparing, OracIe automatically compares by the data type of the expression, based on the data type of expr_1.

three

Allowed location

Procedural statements and SQL statements.

4 exampl

4.1 example 1 [numerical]

Expr_1 is numerical. Compare by size.

All are numeric, and the maximum value taken out is 16:

SQL > SELECT GREATEST (2,5,12,3,16,8,9) A FROM DUAL; A-16

Some are numeric, but strings can be converted to numeric by implicit type conversion based on the data type of expr_1:

SQL > SELECT GREATEST (2,'5, 12, 3, 16, 8, 9) A FROM DUAL; A-16

Part of the string is numeric, but a string that cannot be converted to a numeric type through an implicit type will report an error because string A cannot be converted to a numeric type:

SQL > SELECTGREATEST (2, 3, 16, 8, 9) A FROM DUAL;SELECTGREATEST (2,'A, 12, 3, 16, 8, 9) A FROM DUAL

ORA-01722:

Invalid number

4.2 example 2 [string]

Expr_1 is character type. Compare by acronym (down if equal)

All character type, take out the maximum value G:

SQL > SELECT GREATEST ('Aids,' Bads, 'Cruises,' dudes, 'ejaculations,' Flying dudes, 'Flying Magna G') A FROM DUAL;A-G

All character type, the initials are equal:

SQL > SELECT GREATEST ('Aids,' Bads, 'Cards,' dudes,'E-maths, Gaga, 'GAB') A FROM DUAL;A---GAB

Part of it is character type, which converts non-character type to character type:

SQL > SELECT GREATEST ('Aids, 6, 7, 5000,' ejaculations, 'Flying recordings G') A FROM DUAL;A-G

4.3 example three [time]

Expr_1 is the time type.

All of them are time types:

SQL > SELECTGREATEST (sysdate,TO_DATE ('2014-08-01)) A FROM DUAL;A-2014/8/1

Part of it is a time type, and implicit type conversion cannot be performed:

SQL > SELECTGREATEST (sysdate,'2014-08-01') A FROMDUAL;SELECTGREATEST (sysdate,'2014-08-01') A FROMDUAL

ORA-01861: text does not match format string

4.4 example four [null]

When using GREATEST to take the maximum value, when expr is a function, it will inevitably produce a null value. If a null value is generated, what will the function GREATEST do with that:

When expr_1 is NULL:

SQL > SELECT GREATEST (NULL, 'baked,' clocked, 'dashed,' Eagle, 'GAB') A FROM DUAL;A-

When expr_1 is not NULL and other expr is NULL:

SQL > SELECT GREATEST ('Aging,' dating, 'caching,' dating, 'GAB',' null, 'GAB') A FROM DUAL;A- about how to use greatest function and least function in oracle is here. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.