suppose we have a table in which 200 rows. i want to find
101 row ? what the query....
and how we find 4th and 5th highest salary and 1 to 10
highest salary
Answers were Sorted based on User's Feedback
Answer / umesh h
Use Minus Operator
select * from emp where rownum<=101
minus
select * from emp where rownum<=100;
Here minus will display data only from first query minusing
from 2 query.
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / vishnu
if you have table temp_test1
you fire
select * from temp_test1
now you want 101 rows
basic thing here is records are sorted by rowid as per
insertion
for 101 th row
select * from temp_test1 where rowid in(
(select max(rowidtochar(rowid)) from temp_test1 where
rownum<102))
this is asked in tech mahindra
Is This Answer Correct ? | 3 Yes | 1 No |
Answer / meher
Let the table name is EMP
To find 101st row the query is as below:
select * from EMP where rownum <= 101
minus
select * from EMP where rownum <= 100;
for Nth salary the query is as below:
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT
(DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / priya
Here It is asked for 101 row. It does not mean ascending or
descending order.
So query goes like this
Ans:select * from emp where rowid in(
select max(no) from
(select rowid as no,sal from emp where rownum<102));
To Find the 101th row higest salary
Ans:
select max(sal) from (
select * from emp order by sal) s where rownum<102;
similarly for 4th highest it is 5 and for (n-1)th sal it is
n.
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / hemant
select * from emp where (rowid,0) in
(select rowid,mod(rownum,101) from emp)
and rownum=1
Is This Answer Correct ? | 4 Yes | 4 No |
Answer / m.raghu
answer for 101th record
select * from emp where empno in( select decode
(rownum,&n,empno) from emp);
for 4th highest sal
select distinct sal from(select empno,dense_rank() over
(order by sal desc) rnk from emp) where rnk=&n;
for 5th highest sal give n value=5
for 1-10
select distinct sal from(select empno,dense_rank() over
(order by sal desc) rnk from emp) where rnk<=&n;
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / basavaraj yadwad
Let Table name : Employee
Let the columns: Employee_name, Salary
To find 101st row:
select * from (select * from Employee order by
emplayee_name) where rownum = 101
To find 4th highest salary
select * from (select * from Employee order by salary desc)
where rownum = 4
To find 5th highest salary
select * from (select * from Employee order by salary desc)
where rownum = 4
To find 1 to 10 highest salary
select * from (select * from Employee order by salary desc)
where rownum < 11
Is This Answer Correct ? | 9 Yes | 15 No |
What is correlated sub-query?
how to create a table index in mysql? : Sql dba
Which one of the following is a reason that an INSERT statement might cause an error instead of executing correctly? 1. The INSERT statement was attempting to insert a record into a view that was created from more than one table. 2. The INSERT statement was attempting to insert a record using a combination of constants and values from an existing table. 3. The INSERT statement was attempting to insert a record with a non-NULL value into a table that has that column defined as NULL. 4. The INSERT statement was attempting to insert a record into a table by selecting a record from that same table. 5. The INSERT statement was attempting to insert a record into a view rather than a table.
Is big data nosql?
i have a table emp and columns ename,empno,mgr_id,i need ename,manager name as result i.e employee respective manager.. example empno ename mgr_id 1 john 3 2 paul 3 3 smith 1 4 kevin 1 5 stewart 2 result has to look like this ename manager john smith paul smith smith john kevin john stewart paul can u plz help me out in this.....
what is a composite primary key ? : Sql dba
What are the topics in pl sql?
what are all types of user defined functions? : Sql dba
Is and as keyword in pl sql?
What is not equal in sql?
What is string join?
how many no of table can be join in a sql query.