Convert SQl query to Oracle 10g query

Question

5.00/5 (1 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

SQL

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 7-Feb-13 16:20pm

7prince

Updated 7-Oct-13 1:48am

Aarti Meswania

v3

Add a Solution

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)

Amir Farid · Answer 1 · 2013-10-07T00:49:00

Solution 1

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

SQL

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 :

SQL

SELECT *
FROM 
(SELECT empno, ename, orig_salary, ROWNUM as toprank
FROM employee)
WHERE toprank <= 2

Posted 7-Oct-13 0:49am

Amir Farid

Updated 7-Oct-13 10:10am

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