order by clause is not working in temp table?

Question

5.00/5 (1 vote)

See more:

hi, i want to Find sum of two maximum salary from employee table in oracle.
my employee table as

EMP_ID               EMP_NAME              CONTACTNO     SALARY
-------------------- -------------------- ---------- ----------
Emp01                Mohan Chandra        9560498289      16500 
Emp02                Dinesh               9856895689      21000 
Emp03                Sachin               7858968956      18000 
Emp04                Ajay                 9136564555       9500 
Emp05                Sneha                7585963212      10625 
Emp06                Rahul                9163568958      11300 
Emp07                Divya                7539895689       8500 
Emp08                Nisha                9758695645      11485

now i am writing query as

create table temp  AS SELECT salary  from EMPLOYEE
where rownum<3 order by salary desc;

select sum(salary) from TEMP;

but it is showing result 37500 instead of 39000.
when i written query to see temp table so it shows

 SALARY
----------
     21000 
     16500

instead of

 SALARY
----------
     21000 
     18000

so what should i do for this.
i want to find sum of two maximum salary from employee table.

my another question is i want to select empname,salary, bonus.and bonus column will be three months of salary per employees.so what should i do?please give me solution.

    EMP_NAME          Salary     Bonus
-------------- ----------            ----------
         Mohan Chandra     16500      49500
         Dinesh            21000      63000
   .
   .
   .
   .
   .
and so on

Posted 7-Jan-16 2:08am

Member 10192073

Updated 7-Jan-16 20:20pm

v2

Add a Solution

Comments

Mohibur Rashid 7-Jan-16 8:23am

Why do you need temporary table?
Try something similar
'Select sum(a) from (select sal as a from table order by sal desc limit 2)'

Mohibur Rashid 7-Jan-16 8:24am

For second part, i would be far more work

Member 10192073 8-Jan-16 1:59am

ya actually i am using oracle but limit will be in my sql,
but your logic helped me its working fine.
in oracle it is
select sum(salary) from (select salary from employee order by salary desc)
where rownum<3;

we can do this by using temp table also like as

create table temp as select salary from(select salary from employee order by
salary desc)where rownum<=2;

select sum(salary) from temp;
drop table temp;

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2016-01-07T03:25:00

Solution 1

Why to force doors wide open?

Oracle well know RANK function[^], which provides a way to get k-th value of salary:

SQL

select SUM(salary) sumof2salaries from (
  select e.*, row_number() over (order by salary desc) rn
  from emp e
)
where rn in (1, 2);

Other resources:
RANK[^]
IN Condition[^]
sql - How can I select the record with the 2nd highest salary in database Oracle? - Stack Overflow[^]

[EDIT]
If you would like to get salary + bonus for each user for 3 months, you have to change your query this way:

SQL

select empname, SUM(e.salary + e.bonus) totalSalary
from emp e
where e.paydate between to_date ('2015/10/01', 'yyyy/mm/dd') and to_date ('2015/12/31', 'yyyy/mm/dd')
group by empname

BETWEEN Conditions[^]
Oracle/PLSQL: BETWEEN Condition[^]

Posted 7-Jan-16 3:25am

Maciej Los

Updated 7-Jan-16 20:19pm

v3

Comments

Member 10192073 8-Jan-16 2:04am

give me solution of my second question please

Maciej Los 8-Jan-16 2:13am

I'd like to help you, but i can't. I can't read direct from your screen or in your mind. You didn't provide any valuable information about time period, which is necessary to get salary + bonus for 3 months. Improve your question (use "Improve question" widget) and i'll promise to point you in right direction.
[edit]
I have made some changes in my solution. I hope that may help you to find solution.
Cheers,
Maciej

Member 10192073 8-Jan-16 2:20am

i have improved sir,now give me solution please

Maciej Los 8-Jan-16 2:24am

As i mentioned it earlier, i understand what you want to achieve, but there is no information about time period. Please, see updated answer.