pinky

• May 31st, 2017

SELECT * FROM (SELECT DEPT_ID , DENSE_RANK(SALARY) OVER(PARTITION BY DEPT_ID ORDER BY SALARY ) DRANK FROM DEPARTMENTS ) WHERE DRANK = 2;

KAMLESH

• Dec 29th, 2016

Code
SELECT DEPARTMENT_ID,SALARY FROM
(SELECT DEPARTMENT_ID,SALARY,DENSE_RANK() OVER 
(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) 
AS RNK FROM EMPLOYEES) WHERE RNK=2;

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

maverickwild  

 Profile Answers by maverickwild Questions by maverickwild

• Member Since Nov-2005 | Nov 15th, 2005

---

Try this

select sal from (select * from order by sal asc) where rownum < 3

snehal Falke

• Jul 13th, 2012

Select * FROM emp ORDER BY sal ASC LIMIT 2

mohit

• Jun 28th, 2012

If you want to employee name then query is given below

Code
SELECT ename,sal FROM (SELECT * FROM emp ORDER BY sal ASC)
WHERE rownum<=2;

Improve Answer

Read Best Answer

Editorial / Best Answer

Answered by: Jayakumar M

• Nov 8th, 2005

---

Yes. We can call Functions from SQL statements.

To be callable from SQL statements, a stored function must obey the following

"purity" rules, which are meant to control side effects:

 When called from a SELECT statement or a parallelized INSERT, UPDATE, or

DELETE statement, the function cannot modify any database tables.

 When called from an INSERT, UPDATE, or DELETE statement, the function

cannot query or modify any database tables modified by that statement.

 When called from a SELECT, INSERT, UPDATE, or DELETE statement, the

function cannot execute SQL transaction control statements (such as COMMIT),

session control statements (such as SET ROLE), or system control statements

(such as ALTER SYSTEM). Also, it cannot execute DDL statements (such as

CREATE) because they are followed by an automatic commit.

planet100

 Profile Answers by planet100 Questions by planet100

• Apr 19th, 2011

I have a question... what if the function returns long??i am not able to use in the select query.It says " ORA-00997: illegal use of LONG datatype " .Is there any other way to address this, ...

nis1981

 Profile Answers by nis1981 Questions by nis1981

• Aug 3rd, 2008

Yes, you can call function in sql query, these functions should be in-built or predefined in the DBMS.

Improve Answer