Click here to Skip to main content
14,332,103 members
Rate this:
Please Sign up or sign in to vote.
See more:
Convert SQL query to Oracle 10g query

Asked by: ocdc
How can I convert the query below to Oracle query? . Oracle doesn't use TOP 1 WITH TIES
SELECT      TOP 1 WITH TIES 
        EmployeeID
      , EmployeeName
      , Profit AS "2ndTopProfit"

FROM (SELECT TOP 2 WITH TIES

             S.EmpID AS EmployeeID
           , S.EName AS EmployeeName
           , '$' + STR((SUM(OI.Qty * I.Price) - S.Salary), 8, 2) AS Profit
       FROM SALESPERSONS S LEFT JOIN ORDERS O      ON S.EmpID = O.EmpID
                           LEFT JOIN ORDERITEMS OI ON O.OrderID = OI.OrderID
                           LEFT JOIN INVENTORY I   ON OI.PartID = I.PartID
       GROUP BY S.EmpID, S.EName, S.Salary
       ORDER BY (SUM(OI.Qty * I.Price) - S.Salary) DESC ) AS NEWTABLE   -- need table name in FROM clause

ORDER BY 3 ASC;  -- puts second most profitable person at top of output
Posted
Updated 7-Oct-13 1:48am
v3

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

hi , Oracle doesn't use "TOP 1 WITH TIES" , but you can use rank() over() function instead of it.
As following example :

SELECT *
FROM
(SELECT empno, ename, orig_salary,
DENSE_RANK() OVER(ORDER BY orig_salary desc) toprank
FROM employee)
WHERE toprank <= 2


RANK and DENSE_RANK handle ties

Or you can use the ROWNUM to get the first record
As following example :

SELECT *
FROM 
(SELECT empno, ename, orig_salary, ROWNUM as toprank
FROM employee)
WHERE toprank <= 2
   
v4
Comments
Member 10381235 5-Nov-13 7:24am
   
nice that's it
eivan1815 5-Nov-13 7:29am
   
this is true
thanks
sahar khalilnejad 5-Nov-13 8:10am
   
thanks
hanie2 5-Nov-13 8:12am
   
thank you

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100