A reader, October 01, 2003 - 11:10 am UTC
Cool...
How to write this query?
a reader, October 01, 2003 - 2:21 pm UTC
acc_no a b
====== == ==
100 1 20
100 2 30
100 3 40
200 3 20
200 4 50
I want to get something like this
acc_no c (where c is the derived col)
====== ==
100 15
200 10
..
..
for each distinct acc_no in the table I want to generate a derived col c=sum(b)/sum(a)
that is for acc_no =100
sum(a)=1+2+3=6
sum(b)=20+30+40=90
so the value in dervied col c =90/6=15
Could you help me how to write this using analytics
October 01, 2003 - 2:31 pm UTC
no analytics, just
select acc_no, suM(b)/sum(a) from t group by acc_no
if sum(a) could be zero,
select acc_no, decode(sum(a), 0, to_number(null), sum(b)/sum(a) ) from t group by acc_no
Query Re. Second highest..
Swap, October 02, 2003 - 6:13 am UTC
Hi!
In the first question Bipin asked about second highest record for the following data
-------------
Ram 10 1000
Jai 10 2000
San 20 3000
Das 20 1500
Has 30 4000
I should get
------------
Ram 10 1000
Das 20 1500
Has 30 4000
Following query will return only 2 records as there is only one record for dept = 30 .
1 select *
2 from (select deptno,sal,
3 dense_rank() over
4 (partition by deptno order by sal desc ) dr
5 from emp )
6* where dr = 2
scott@ORA920LAP> /
DEPTNO SAL DR
---------- ---------- ----------
10 2450 2
20 2975 2
30 1600 2 <==== This record wil not be there using above mentioned set of data , instead if there are more than one row for deptno=30 then it will return the data as above...
Am I right?
October 02, 2003 - 7:59 am UTC
that is correct -- i answered his text question, ignored the "sample" output.
MAX_1 TO MAX_10 (TOP 10 HIGHEST SCORES)
ramis, September 29, 2005 - 6:47 pm UTC
Hi,
I want to have two different outputs that will show me the top 10 highest student class scores in a single query based on the following criteria:
1. The first query should deal the scores in such a way that if there is tie for any spot (two or more same max scores) then all the tied max. scores come one by one and then the next highest score..
For example, suppose if for any student the maximum score is 100 and it occurs three times the 100 would come at the first three max.(1 to 3) positions and so on with next highest score for coming to next place
the output could be like this
ID MAX_1 MAX_2 MAX_3 MAX_4 MAX_5 MAX_6 MAX_7 MAX_8 MAX_9 MAX_10
1361 100 100 100 99 91 76 76 51 22 9
2. The second query should deal such that if there is tie for any spot (two or more same max scores) then it will select only one of them for the respective spot and then next best score, lower than the previous best, would come to the next place.
For example, if for any student the maximum score is 100 and it occurs three times the 100 would come at the first place ONLY and on the second place the next best score (lower then 100 would come) and so on..
the output could be like this
ID MAX_1 MAX_2 MAX_3 MAX_4 MAX_5 MAX_6 MAX_7 MAX_8 MAX_9 MAX_10
1361 100 99 91 76 51 22 9 8 0
in both queries if the there is no data found or no next best then NULL should come
CREATE TABLE T
(STUDENT_ID NUMBER(4),
SCORES NUMBER(3))
INSERT INTO T VALUES (1361,100);
INSERT INTO T VALUES (1361,11);
INSERT INTO T VALUES (1361,100);
INSERT INTO T VALUES (1361,100);
INSERT INTO T VALUES (1361,99);
INSERT INTO T VALUES (1361,91);
INSERT INTO T VALUES (1361,76);
INSERT INTO T VALUES (1361,76);
INSERT INTO T VALUES (1361,51);
INSERT INTO T VALUES (1361,22);
INSERT INTO T VALUES (1361,9);
INSERT INTO T VALUES (1061,8);
INSERT INTO T VALUES (1061,0);
INSERT INTO T VALUES (1061,18);
INSERT INTO T VALUES (1061,14);
INSERT INTO T VALUES (1061,13);
INSERT INTO T VALUES (1061,12);
INSERT INTO T VALUES (1061,33);
INSERT INTO T VALUES (1061,10);
INSERT INTO T VALUES (1061,0);
INSERT INTO T VALUES (1061,5);
INSERT INTO T VALUES (1061,41);
INSERT INTO T VALUES (2361,44);
INSERT INTO T VALUES (2361,7);
INSERT INTO T VALUES (2361,10);
INSERT INTO T VALUES (2361,49);
INSERT INTO T VALUES (2361,12);
INSERT INTO T VALUES (2361,0);
INSERT INTO T VALUES (1361,0);
INSERT INTO T VALUES (1960,100);
INSERT INTO T VALUES (1960,68);
INSERT INTO T VALUES (1960,14);
INSERT INTO T VALUES (1960,89);
INSERT INTO T VALUES (1960,102);
INSERT INTO T VALUES (1960,45);
INSERT INTO T VALUES (1960,63);
INSERT INTO T VALUES (1960,44);
INSERT INTO T VALUES (1960,86);
INSERT INTO T VALUES (1960,5);
INSERT INTO T VALUES (1960,3);
INSERT INTO T VALUES (1960,8);
INSERT INTO T VALUES (1960,27);
INSERT INTO T VALUES (1960,28);
INSERT INTO T VALUES (1960,141);
INSERT INTO T VALUES (1960,0);
INSERT INTO T VALUES (1960,7);
INSERT INTO T VALUES (1960,37);
i would be most grateful if the query is simaple, fast and easy as it has to deal with 100000 records in a table
thanks in advance
regards
September 30, 2005 - 8:51 am UTC
... i would be most grateful if the query is simaple, fast and easy as it has to
deal with 100000 records in a table...
hehehe. good.
ops$tkyte@ORA10G> select student_id,
2 max( decode( rn, 1, scores ) ) maxa_1,
3 /* ... you would have rn, 2 and 3 and 4 and so on here */
4 max( decode( rn, 10, scores ) ) maxa_10,
5 max( decode( dr, 1, scores ) ) maxb_1,
6 /* ... you would have dr, 2 and 3 and 4 and so on here */
7 max( decode( dr, 10, scores ) ) maxb_10
8 from (
9 select student_id,
10 scores,
11 row_number() over (partition by student_id order by scores desc nulls last) rn,
12 dense_rank() over (partition by student_id order by scores desc nulls last) dr
13 from t
14 )
15 where dr <= 10
16 group by student_id
17 /
STUDENT_ID MAXA_1 MAXA_10 MAXB_1 MAXB_10
---------- ---------- ---------- ---------- ----------
1061 41 0 41 0
1361 100 11 100
1960 141 37 141 37
2361 49 49
It'll be most efficient to get both at the same time. maxa is query 1, maxb is query 2
another way to get max sal
amithradarapu, July 09, 2008 - 5:32 am UTC
SELECT RANK, sal FROM (select Sal,
dense_rank() OVER (ORDER BY sal desc )
from emp ) WHERE RANK <=5
July 09, 2008 - 10:46 am UTC
well, umm, that is sort of the original answer... but sure.
SQl-2nd higest salary
Lokesh, April 28, 2010 - 11:58 pm UTC
select *
from (select department_id,salary,
dense_rank() over
(partition by department_id order by salary desc ) dr
from employees )
where dr = 2
by using this code
we can find 2nd higest BUT
if department having only one record at that time this code fails
what modification need to do..?
April 29, 2010 - 7:35 am UTC
if there is no 2nd highest - you tell me - what should it do?
we cannot modify something without a specification that tells us what to return.
Your question was stated as: "report on the 2nd highest salary"
If there is no 2nd highest salary, you get nothing - you told us "want 2nd highest". If there is NO 2nd - what do you expect to see???? should we make up some data? what should be reported!!!
be precise, else you might as well just query
select dbms_random.random from dual;
query to include the single record DEPARTMENT also....
KRISHNAMURTHY L S, June 13, 2010 - 6:48 am UTC
SQL> ed
Wrote file afiedt.buf
1 select * from (SELECT DEPTNO,ENAME,SAL ,rank () over (partition by deptno order by sal asc ) r
2 FROM EMP
3 GROUP BY DEPTNO,ENAME,SAL
4 ORDER BY DEPTNO,SAL )
5 where rank=2
6 union
7 select deptno,ename,sal,1 from emp
8 where deptno in (SELECT DEPTNO FROM EMP
9* GROUP BY DEPTNO having count(*) <=1)
SQL> /
10 CLARK 2450 2
20 ADAMS 1100 2
30 MARTIN 1250 2
30 WARD 1250 2
40 KRISH 2300 1
June 22, 2010 - 8:02 am UTC
they never said what they wanted to have happen.
there are definitely more efficient ways to produce the answer of "give me the second highest, or if there isn't a second highest, the highest salary".
why do you have an order by on line 4? it is not useful to the answer, it doesn't cause anything to be sorted.
why do you group by in the query on line 3? It doesn't make sense do to that.
there is no column RANK to be seen, the where clause on line 5 doesn't make sense.
ops$tkyte%ORA11GR2> create table emp as select * from scott.emp;
Table created.
ops$tkyte%ORA11GR2> insert into emp (deptno,ename,sal) values ( 40, 'loner', 100 );
1 row created.
ops$tkyte%ORA11GR2> select *
2 from (
3 select deptno, ename, sal,
4 rank() over (partition by deptno order by sal) r,
5 max(sal) over (partition by deptno) max_sal
6 from emp
7 )
8 where r = 2
9 or (r=1 and sal=max_sal)
10 order by deptno, sal
11 /
DEPTNO ENAME SAL R MAX_SAL
---------- ---------- ---------- ---------- ----------
10 CLARK 2450 2 5000
20 ADAMS 1100 2 3000
30 MARTIN 1250 2 2850
30 WARD 1250 2 2850
40 loner 100 1 100
Prev query
jan, July 01, 2010 - 12:01 pm UTC
The query in the previous review seems to skip rows on occasion, when the number 2 salaries are rows 2 and three in a department, so they both get rank 3.
What about:
select *
from (
select deptno, ename, sal,
rank() over (partition by deptno order by sal desc) r,
max(sal) over (partition by deptno) max_sal,
count(*) over (partition by deptno) num_emp
from emp
)
where r = 2
or num_emp=1
order by deptno, sal
/
?
July 06, 2010 - 2:22 pm UTC
give example.
Example
jan, July 06, 2010 - 4:42 pm UTC
create table emp(deptno number, ename varchar2(100), sal number);
insert into emp values( 10, 'Dep10_1', 5000);
insert into emp values( 10, 'Dep10_2', 2450);
insert into emp values( 10, 'Dep10_3', 1450);
insert into emp values( 20, 'Dep20_1', 5000);
insert into emp values( 20, 'Dep20_2_a', 1100);
insert into emp values( 20, 'Dep20_2_b', 1100);
insert into emp values( 30, 'Dep30_1', 5000);
insert into emp values( 30, 'Dep30_2a', 1250);
insert into emp values( 30, 'Dep30_2b', 1250);
insert into emp values( 30, 'Dep30_3', 1000);
insert into emp values( 40, 'loner', 100);
commit;
select *
from (
select deptno, ename, sal,
rank() over (partition by deptno order by sal) r,
max(sal) over (partition by deptno) max_sal
from emp
)
where r = 2
or (r=1 and sal=max_sal)
order by deptno, sal
/
DEPTNO ENAME SAL R MAX_SAL
10 Dep10_2 2450 2 5000
30 Dep30_2a 1250 2 5000
30 Dep30_2b 1250 2 5000
40 loner 100 1 100
select *
from (
select deptno, ename, sal,
rank() over (partition by deptno order by sal desc) r,
max(sal) over (partition by deptno) max_sal,
count(*) over (partition by deptno) num_emp
from emp
)
where r = 2
or num_emp=1
order by deptno, sal
/
DEPTNO ENAME SAL R MAX_SAL NUM_EMP
10 Dep10_2 2450 2 5000 3
20 Dep20_2_a 1100 2 5000 3
20 Dep20_2_b 1100 2 5000 3
30 Dep30_2a 1250 2 5000 4
30 Dep30_2b 1250 2 5000 4
40 loner 100 1 100 1
July 06, 2010 - 4:48 pm UTC
but I'm not sure what I'm supposed to be looking at.
You see - you are asking something "new", you didn't ask the original questions - I'm not sure what you are trying to point out or what me to look at specifically?
Clarification...
jan, July 07, 2010 - 11:52 am UTC
When looking at the followup above to "query to include the single record DEPARTMENT also...." it looked to me that the query
ops$tkyte%ORA11GR2> select *
2 from (
3 select deptno, ename, sal,
4 rank() over (partition by deptno order by sal) r,
5 max(sal) over (partition by deptno) max_sal
6 from emp
7 )
8 where r = 2
9 or (r=1 and sal=max_sal)
10 order by deptno, sal
11 /
was skipping some rows that it shouldn't. I tried to show that in the response above, where in my test case the rows for ename Dep20_2_a and Dep20_2_b do not appear when running that query. My impression was that they should have shown up, since both of them are the "number 2" salary in their department.
July 08, 2010 - 12:12 pm UTC
oh, just using dense_rank() would achieve what you wanted then.
If there are two "first place" rows - there is by definition no "second place row" - there is no second - with rank.
with dense rank there is.
ops$tkyte%ORA11GR2> select *
2 from (
3 select deptno, ename, sal,
4 DENSE_rank() over (partition by deptno order by sal) r,
5 max(sal) over (partition by deptno) max_sal
6 from emp
7 )
8 where r = 2
9 or (r=1 and sal=max_sal)
10 order by deptno, sal
11 /
DEPTNO ENAME SAL R MAX_SAL
---------- --------------- ---------- ---------- ----------
10 Dep10_2 2450 2 5000
20 Dep20_1 5000 2 5000
30 Dep30_2a 1250 2 5000
30 Dep30_2b 1250 2 5000
40 loner 100 1 100
mo sh, July 07, 2010 - 6:13 pm UTC
select deptno , max(distinct sal) from emp e
where sal <> ( select max(sal) from emp
where e.deptno = deptno )
group by deptno
This is awsome
pravin, October 16, 2012 - 4:39 am UTC
These blogs are very useful in day to days IT professional life. We all are expecting to get solution as fast as possible, we search a lot on internet, but here you get everything under one roof.
A reader, October 09, 2017 - 12:39 am UTC
Display the top 5 students and then the next 5 students, that is, ranked 6 to 10
October 09, 2017 - 2:55 pm UTC
Top 5 students based on what?
Oracle 12c has some extra tools
Haakon, October 10, 2017 - 10:26 am UTC
If you use 12c or newer, you can do something like this:
select empname, deptno, sal from employee order by sal desc offset 1 rows fetch next 1 rows only;
(I tested it on v$sql like so:
SELECT SHARABLE_MEM, SQL_TEXT FROM v$sql order by SHARABLE_MEM DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;)
This maybe easier to use and faster to do on one-off queries. If you just do this kind of query manually, you may even just to SELECT (...) fetch next 2 rows only; and read the second result.
I haven't tested the solutions against each other, and one may be much faster and/or better in other ways though.
October 10, 2017 - 1:53 pm UTC
Nice input.
OFFSET my FETCH
Duke Ganote, October 10, 2017 - 6:10 pm UTC
I use FETCH frequently, and OFFSET rarely. They can be useful in this case, but only if SAL is unique... which it isn't for the stereotypical EMP records:
select empno, ename, sal
, dense_rank()over(order by sal desc) dr#
, row_number()over(order by sal desc) R#
from ccistg.emp
order by dr# asc;
EMPNO ENAME SAL DR# R#
----- ---------- ---------- ---------- ----------
7839 KING 5000 1 1
7902 FORD 3000 2 2
7788 SCOTT 3000 2 3
7566 JONES 2975 3 4
7698 BLAKE 2850 4 5
7782 CLARK 2450 5 6
7499 ALLEN 1600 6 7
7844 TURNER 1500 7 8
7934 MILLER 1300 8 9
7521 WARD 1250 9 10
7654 MARTIN 1250 9 11
7876 ADAMS 1100 10 12
7900 JAMES 950 11 13
7369 SMITH 800 12 14
select empno, ename, sal
, dense_rank()over(order by sal desc) dr#
, row_number()over(order by sal desc) R#
from ccistg.emp
order by dr# asc
offset 5 rows
fetch next 5 rows only;
EMPNO ENAME SAL DR# R#
----- ---------- ---------- ---------- ----------
7782 CLARK 2450 5 6
7499 ALLEN 1600 6 7
7844 TURNER 1500 7 8
7934 MILLER 1300 8 9
7521 WARD 1250 9 10
October 11, 2017 - 1:07 pm UTC
Yep, you need to be careful when paginating by non-unique values, the results are non-deterministic!
I'm not sure what you're trying to show with the example though. You are fetching rows 6-10, which is what you asked for.