Skip to main content

Question 17:How to get the 'n' th maximum value of a column?

We can get the nth maximum value through query by using the combination of order by clause and rownum pseudo column.

The Query following.

SELECT MIN(columnname)
FROM ( SELECT coumnname FROM tablename ORDER BY columnname DESC)
WHERE rownum <= n

Comments

Post a Comment

Popular posts from this blog

Question 8: Aggregate Functions

Examine the description of the STUDENTS table: STD_ID NUMBER(4) COURSE_ID VARCHARD2(10) START_DATE DATE END_DATE DATE Which two aggregate functions are valid on the START_DATE column? (Choose two) A. SUM(start_date) B. AVG(start_date) C. COUNT(start_date) D. AVG(start_date, end_date) E. MIN(start_date) F. MAXIMUM(start_date) Answer: C & E Explanation: It is possible to apply COUNT() and MIN() functions on the column with DATE data type. Incorrect Answers A: Function SUM() cannot be used with DATE data type column. B: Function AVG() cannot be used with DATE data type column. D: Function AVG() cannot be used with DATE data type column, and function AVG() just has one parameter X, not two. It averages all X column values returned by the SELECT statement. F: There is no MAXIMUM() function in Oracle, only MAX() function exists.
Post a Comment
Read more

19. Oracle Processes

Processes Oracle uses many small (focused) processes to manage and control the Oracle instance. This allows for optimum execution on multi-processor systems using multi-core and multi-threaded technology. Some of these processes include: PMON - Process Monitor SMON - System Monitor ARCn - Redo Log Archiver LGWR - Redo Log Writer DBWn - Database Writer CKPT - Checkpoint process RECO - Recoverer CJQn - Job Queue Coordinator QMNn - Queue-monitor processes Dnnn - Dispatcher Processes (multiplex server-processes on behalf of users) Snnn - Shared server processes (serve client-requests) MMAN - Internal process (used for internal database tasks) LSP0 - Logical standby coordinator process (controls Data Guard log-application) MRP - Media-recovery process (detached recovery-server process) MMON - Memory-monitor process MMNL - Memory monitor light (gathers and stores AWR statistics) PSP0 - Process-spawner (spawns Oracle processes) RFS - Remote file server process (archive to a remote site) DBRM ...
Post a Comment
Read more

20. TRIM Function

Which SELECT statement will the result 'elloworld' from the string 'HelloWorld'? A. SELECT SUBSTR( 'HelloWorld',1) FROM dual; B. SELECT INITCAP(TRIM ('HelloWorld', 1,1)) FROM dual; C. SELECT LOWER(SUBSTR('HellowWorld', 1, 1) FROM dual; D. SELECT LOWER(SUBSTR('HelloWorld', 2, 1) FROM dual; E. SELECT LOWER(TRIM ('H' FROM 'HelloWorld')) FROM dual; Answer: E Explanation: TRIM function accept a string describing the data you would like to trim from a column value. It can trim from both side of column value i.e. left and right. In the following statement this functionwill trim as SELECT LOWER(TRIM ('+' FROM 'HelloWorld')) FROM dual; From the above statement trim function will remove the character 'H' from 'HelloWorld' and LOWER function will convert the remaining character to lower case.
Post a Comment
Read more