DENSE_RANK returns a NUMBER representing the rank of a row within a group of rows. Syntax: DENSE_RANK(expression1,…) WITHIN GROUP (ORDER BY) Example: SELECT DENSE_RANK(5000,103) WITHIN GROUP (ORDER BY SALARY, MGR_ID) as RESULT FROM EMP; RESULT ———— 43
Group_id – Oracle SQL Function
GROUP_ID assigns a number to each group defined in a GROUP BY clause, GROUP_ID can be used to easily see duplicated groups in query results. Example: select avg(salary), mgr_id, group_id() gid from EMP group by mgr_id; AVG(SALARY) MGR_ID GID ———————-
Decode – Oracle SQL Function
The decode function has the functionality of an IF-THEN-ELSE statement. The syntax for the decode function is: decode ( expression , search , result [, search , result]… [, default] ) expression is the value to compare. search is the
Max – Oracle SQL Function
MAX returns the maximum value of the expression argument. Syntax: MAX(expression) Example: SELECT max(salary) as max_salary from JOBS; max_salary ——————- 40000
Instr – Oracle SQL Function
The instr function returns the location of a substring in a string. The syntax for the instr function is: instr ( string1 , string2 , [ start_position ], [ nth_appearance ]) string1 is the string to search. string2 is the
Median – Oracle SQL Function
MEDIAN returns the median value of the expression argument. Syntax: MEDIAN(expression) Example: SELECT MEDIAN(max_salary) as med FROM JOBS; MED —————- 10000
Floor – Oracle SQL Function
The floor functions allows you to round down any number to the nearest integer. For example: floor(6.02) would return 6 floor(7.789) would return 7 See Ceil to round numbers up to the nearest integer. See Round to round a number
Rank – Oracle SQL Function
RANK determines the rank of one or more of the expression exp within a result set. Syntax: RANK(expression…) WITHIN GROUP (ORDER BY expression) Example: SELECT RANK(10000) WITHIN GROUP (ORDER BY salary DESC) as RESULT FROM EMP; RESULT ——————- 16
Ceil – Oracle SQL Function
The ceil functions allows you to round up any number to the next integer. For example: ceil(6.02) would return 7 ceil(7.59) would return 8 See Round to round numbers to a particular decimal place See Floor to round a number
Stddev – Oracle SQL Function
STDDEV returns the sample standard deviation of the expression. STDDEV will return zero when it has only one row of input data. Syntax: STDDEV(expression) Example: SELECT STDDEV(COMMISSION_PCT) FROM EMP; STDDEV(COMMISSION_PCT) ——————————————– .0857432345