ViKu ViKu - 1 year ago 68
SQL Question

Query to find the salary of an employee

I'm writing a query in Oracle as follows

select rownum as Salary, (rownum + 1) as Increment,(rownum + (rownum + 1)) as Total from dual;

And result is as follows

Salary Increment Total
1 2 3

But i'm trying for to get following result

Salary Increment Total
1 2 3
3 2 5
5 2 7

Here in above result Total of 1st row will become salary in the next row,
Increment amount will be 2 for an instance, and total of 2nd row will become salary in the 3rd row and so on. help me in getting this result.

Answer Source

This query will give you that result. Use connect by level to create 3 rows. Use lag() to retrieve previous total. I honestly don't know if this is what you are looking for.

select nvl(lag(totalAux) over (order by SalaryAux), SalaryAux) as Salary,
       totalAux as Total
  from (
  SELECT level        AS SalaryAux,
          2           AS Increament,
        (level * 2)+1 AS TotalAux
  FROM dual
  connect by level <=3);


---------- ---------- ----------
         1          2          3
         3          2          5
         5          2          7
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download