Thread: Give me the queries for.....

suppose i have 50 records of employee table.

1) I want to find the person who is having 22nd highest salary.

2) I want to delete a person which is in 39th row

3) I want to Update rec no 45

for the first one:

select e1.empname,sal from emp_table e1
where (21=(select count(*) from emp_table e2 where e1.sal

Thanks&Regards,
Bhargavi

1.
select e1.empname,sal from emp_table e1
where (21=(select count(*) from emp_table e2
where e1.sal

2.
delete from emp_table where
empname=(select empname from (select rownum r1,empname from emp_table) where r1='39');

here in place of empname field u can use any primary key or unique key field.


3.
similar to delete operation u can do update also.

update emp_table set empid='1005' where empname=(select empname from (select rownum r1,empname from emp_table) where r1='45');



-Bhargavi

for the first query

where e1.sal
sorry..
i am not able to write less than symbol here. donno y..

where e1.sal is less than e2.sal

this is the condition..


Bhargavi

i am also using one table only.
e1,e2 are just aliases used for emp_table.

u just run that query. u will get the required result.

2nd, 3rd answer is ok. but 1st answer is not able to understand.
There is only one table. from only that table i want to find 22nd highest salary. How it is?

That is a co-related query .In that a table alias is used for the same table and compared with itself. Performance wise co-related queries are the slowest as it executes once for each row in table.

1.select * from (select rownum rnum,sal from(select * from emp order by sal desc))
where rnum=&rnum;

2.delete from emp where (sal,rownum)=(select sal,rownum
from (select rownum rn,sal from(select * from emp order by sal desc))
where rn=&num);

3.update emp set sal=20000 where (sal,rownum)=(select sal,rownum from (select rownum rnum,sal from(select * from emp))
where rnum=&rnum);

Try this

there are several functions to do your job

here i give u a sample query and it's o/p where i used dense_rank() and rank() function as see the o/p and then try to use it in yr query as needed.I have not done the full req.. of yr's do it yrself



with t as
(
select 'ram' as emp,1 as dept,1000 as sal from dual
union
select 'shyam', 2,2000 from dual
union
select 'jadu',1,1000 from dual
union
select 'modhu',1,6000 from dual
union
select 'bp',2,7000 from dual
)
select
emp,dept,sal,drn,rn
from
(select emp,dept,sal,dense_rank() over(partition by dept order by sal)drn
,rank() over(partition by dept order by sal)rn from t)

-------------------------------------------------------------------------
o/p is

EMP DEPT SAL DRN RN
jadu 1 1000 1 1
ram 1 1000 1 1
modhu 1 6000 2 3
shyam 2 2000 1 1
bp 2 7000 2 2

hints.. use where clause in the query to get yr nth data

cheers

Originally Posted by rajaputra

suppose i have 50 records of employee table.
1) i want to find the person who is having 22nd highest salary.
2) i want to delete a person which is in 39th row
3) i want to update rec no 45

answer to question 1--------------- select top 1 salary from aaa where salary in(select top 22 salary from aaa order by salary desc) order by salary ashok choudhary software engineer sceptre solutions bangalore

1.select * from emp m
where (select count(distinct sal) rank from emp s
where s.sal>m.sal)+1=22;


2.delete from emp
where empno=(select empno from (select rownum rn,emp.* from emp)
where rn=39);

3.update emp
set sal=10000
where empno=(select empno from (select rownum rm,emp.* from emp) where rn=45);

1.
select distinct(a.salary)
from emp a
where 22 =
(select count(distinct b.salary)
from emp b
where a.salary>=b.salary);

1 select min(salary)as 22nd_highestsalary
from salary
where salary in ( select top 22 salary
from salary
order by salary desc);

I am doing something similar, very helpful answers here.