display the second lowest salary from emp table without using min & all

Pendo

• Jul 17th, 2021

SELECT Top (1) Salary FROM Employee ORDER BY Salary

Code
 

satyam singh

• Apr 22nd, 2020

"HAVING" clause is required. It works with "GROUP BY" clause

Answer Question  Select Best Answer

please give some sub-query examples based on correlated sub-query and nested

Pallavi g rao

 Profile Answers by Pallavi g rao Questions by Pallavi g rao

• Apr 21st, 2011

correlated:- outer query is executed first and result is sent to inner query for execution       select * from emp e1 where e1.basicsal=(selcte max(basicsal) from emp e2 ...

nandlalseth

 Profile Answers by nandlalseth Questions by nandlalseth

• Aug 14th, 2008

Correlated Sub Query:Select * from emp e where exists (select 1 from dept d where d.deptno = e.deptno)Nested Sub Query:Select * from emp e where deptno in (select deptno from dept d)

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

nirmal1in  

 Profile Answers by nirmal1in Questions by nirmal1in

• Member Since Jan-2010 | Jan 17th, 2010

---

This can be accomplished in following way:

Example: table - emp

For First n rows:

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a < 5 --- say n is 5 display first 5 records

For last n rows:

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename) a
FROM emp) x
WHERE x.a < 5

FROM )WHERE .a<

This query can be used for finding nth row also say n = 5

SELECT * FROM
(SELECT empno,ename,job,row_number() over (order by ename desc) a
FROM emp) x
WHERE x.a = 5

deepthi

• May 17th, 2012

Code
SELECT rownum,a.*FROM(SELECT rownum,b.*FROM emp b ORDER BY rownum DESC) a WHERE rownum <&n;

Vasavi Katakam

 Profile Answers by Vasavi Katakam Questions by Vasavi Katakam

• Jul 18th, 2011

For First N Rows:

Code

1.  
2. SELECT top 5 * FROM table_name
3.  

For Last N Rows:

Code

1.  
2. SELECT * FROM
3. (SELECT top 5 * FROM
4. table_name
5. ORDER BY Columnname DESC)
6. ORDER BY Columnname ASC
7.  

Improve Answer

CREATE TABLE temp(eno NUMBER CONSTRAINTS pk_eno PRIMARY KEY,gender CHAR(1) CHECK (gender IN( 'M','F')));INSERT INTO temp VALUES ('01','M');INSERT INTO temp VALUES ('02','M');INSERT INTO temp VALUES ('03','F');INSERT INTO temp VALUES ('04','M');INSERT INTO temp VALUES ('05','M');INSERT INTO temp VALUES ('06','F');INSERT INTO temp VALUES ('07','M');INSERT INTO temp VALUES ('08','F');COMMIT;UPDATE temp...

Pallavi g rao

 Profile Answers by Pallavi g rao Questions by Pallavi g rao

• Apr 22nd, 2011

Update emp set gender=case when gender='f' Then 'm'else 'f'end

Elena

• Nov 8th, 2007

Update   set gender=(case gender when 'F' then 'M' when 'M' then 'F' end)

Answer Question  Select Best Answer