Answer / nag.vamshi

select* from(select ename,deptno,sal,row_number()
over(partiton by deptno order by sal)num from emp)
where num<=3
order by deptno;

Answer / santo

SELECT r.salary, r.department_id, r.RANK highest
FROM (SELECT salary, department_id,
DENSE_RANK () OVER (PARTITION BY
department_id ORDER BY salary DESC)

RANK
FROM employees) r
WHERE r.RANK <= 3
ORDER BY 2

Answer / venkateswara thiruvedhula

SELECT empno,
deptno,
sal,
RANK() OVER (PARTITION BY deptno ORDER BY sal
desc) "rank"
FROM emp;

EMPNO DEPTNO SAL rank
---------- ---------- ---------- ----------
7934 10 5000 1
7782 10 3000 2
7839 10 2000 3
7369 20 2950 1
7876 20 1700 2
7566 20 1100 3
7788 20 900 4
7902 20 900 4

Answer / shailesh j

SELECT * FROM (
SELECT DEPARTMENT_ID,SALARY,DENSE_RANK() OVER(PARTITION BY
DEPARTMENT_ID ORDER BY SALARY DESC) AS RK FROM EMPLOYEES)
WHERE RK<4;

Answer / ramireddy

select * from emp e where &n=(select count(distinct Sal)from
emp where Sal<=e.Sal);

Answer / sasi

select * from
(select sal,rank() over (order by sal desc) emprank from emp)
where emprank<=3

Answer / keshav

with Q as (select *,dense_rank() over(partition by dept
order by sal) as dr from dept
select * from Q
where dr=3

Answer / chiru

select *
from (select row_number() over(partition by deptno order by sal desc) r,
b.*
from (select rownum num, a.* from emp a order by sal desc) b)
where r <= 3

Answer / divakarreddy

SQL> select *from (select ename,sal,deptno,rank()
2 over(partition by deptno order by sal desc)topsal
3 from emp)
4 where topsal<=3
5 order by deptno,sal desc;

ENAME SAL DEPTNO TOPSAL
---------- ---------- ---------- ----------
KING 5000 10 1
MILLER 3000 10 2
CLARK 2450 10 3
SCOTT 3000 20 1
JONES 2975 20 2
ADAMS 1100 20 3
BLAKE 2850 30 1
ALLEN 1600 30 2
TURNER 1500 30 3

9 rows selected.

Answer / ashwini

Select * from emp A where 2>=(select count(distinct(B.sal)) from emp B where A.sal<B.sal);

Explain the uses of control file.

0 Answers  

Is and as keyword in pl sql?

0 Answers  

Explain the difference between 'between' & 'and' operators in sql

0 Answers  

how mysql optimizes distinct? : Sql dba

0 Answers  

Define tables and fields in a database

0 Answers  

What is the file extension for sql database?

0 Answers  

What is group function in sql?

0 Answers  

What is the Subquery?

4 Answers  

Hi All, I am new to both this blog and technology. I was able to see a response for one of the questions on triggers as below. I would like to know why are we using " if rtrim(to_char(sysdate,'day'))=rtrim('sunday') then" instead, can't we use " if sysdate = 'sunday' then". I can understand the use of "rtrim", but dont know y v r using to_char. I have seen this in many cases but did not get a convincible explaination. Please help me with this and do excuse if this question sounds silly. Thanks in advance...... create or replace trigger trg_sun before insert on <table name> begin if rtrim(to_char(sysdate,'day'))=rtrim('sunday') then raise_application_error(-20345,'no transaction in sunday'); end if; end trg_sun;

2 Answers  

Explain alias in sql?

0 Answers  

What is an example of translating a date into julian format?

0 Answers  

Is a table valued function object?

0 Answers