SQL query modification questions

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

How can I modify the queries below and still get the same result without using TOP?

This query gives the most profitable salesperson:

SQL

SELECT TOP 1  Salespersons.empid, Salespersons.ename,(SUM(ORDERITEMS.qty*INVENTORY.price) - SALESPERSONS.salary) AS 'MostProfitable_SalesPerson'
FROM  Inventory INNER JOIN
               OrderItems ON Inventory.partid = OrderItems.partid INNER JOIN
               Orders ON OrderItems.orderid = Orders.orderid INNER JOIN
               Salespersons ON Orders.empid = Salespersons.empid
               
GROUP BY  Salespersons.empid, Salespersons.ename,SALESPERSONS.salary  
ORDER BY  MostProfitable_SalesPerson DESC;

------------------------------------------------------------------------------

This query gives the second profitable salesperson:

SQL

SELECT TOP 1  Salespersons.empid, Salespersons.ename,(SUM(ORDERITEMS.qty*INVENTORY.price) - SALESPERSONS.salary) AS 'MostProfitable_SalesPerson'
FROM  Inventory INNER JOIN
               OrderItems ON Inventory.partid = OrderItems.partid INNER JOIN
               Orders ON OrderItems.orderid = Orders.orderid INNER JOIN
               Salespersons ON Orders.empid = Salespersons.empid
               
GROUP BY  Salespersons.empid, Salespersons.ename,SALESPERSONS.salary  
ORDER BY  MostProfitable_SalesPerson ASC;

Posted 16-Jan-13 5:48am

7prince

Updated 16-Jan-13 6:43am

Thomas Daniels

v3

Add a Solution

Comments

Zoltán Zörgő 16-Jan-13 12:58pm

What's the difference between these two queries? I don't see any.

[no name] 16-Jan-13 13:27pm

just ORDER BY difference :) ASC & DESC

3 solutions

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)

**Zoltán Zörgő** · Answer 1 · 2013-01-16T07:05:00

In SQL2005 and above, you have ROW_NUMBER[^] function.
Here is the "common sample" with subquery:

SQL

USE AdventureWorks;
GO

SELECT *
FROM 
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader
)
WHERE RowNumber = 5;

In your case, you will need to change the inner query with yours, and of course, you will need to apply the proper OVER clause.

Kishor Deshpande · Answer 2 · 2013-01-16T07:08:00

Solution 2

Link of StackOverflow article

Posted 16-Jan-13 7:08am

Kishor Deshpande

Tharaka MTR · Answer 3 · 2013-01-16T07:34:00

You can use the WITH - Common Table Expression with ROW_NUMBER function for this.

first you have to create a CTE with row number. then it works like following table

empid ename   MostProfitable_SalesPerson ROWNUMBER
13    alferd  1500                       1
34    kevin   1300                       2
3     syard   1100                       3

Then you can select the top most profitable record by rownumber = 1
and 2nd Most rownumber=2 etc ..

SQL

WITH MostProfitable_SalesPersons(empid,ename,MostProfitable_SalesPerson,ROWNUMBER) AS (
	SELECT Salespersons.empid, Salespersons.ename,
(SUM(ORDERITEMS.qty*INVENTORY.price) - SALESPERSONS.salary) AS 'MostProfitable_SalesPerson',
ROW_NUMBER() OVER(ORDER BY SUM(ORDERITEMS.qty*INVENTORY.price) - SALESPERSONS.salary DESC)
FROM  Inventory INNER JOIN
               OrderItems ON Inventory.partid = OrderItems.partid INNER JOIN
               Orders ON OrderItems.orderid = Orders.orderid INNER JOIN
               Salespersons ON Orders.empid = Salespersons.empid
               
GROUP BY  Salespersons.empid, Salespersons.ename,SALESPERSONS.salary )

SELECT empid,ename,MostProfitable_SalesPerson
FROM MostProfitable_SalesPersons
WHERE ROWNUMBER=1

SQL query modification questions

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0