Click here to Skip to main content
13,147,956 members (44,465 online)
Rate this:
 
Please Sign up or sign in to vote.
How can I modify the queries below and still get the same result without using TOP?

This query gives the most profitable salesperson:
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:
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
7prince414
Updated 16-Jan-13 6:43am
ProgramFOX204.6K
v3
Comments
Zoltán Zörgő 16-Jan-13 12:58pm
   
What's the difference between these two queries? I don't see any.
Sheikh Muhammad Haris 16-Jan-13 13:27pm
   
just ORDER BY difference :) ASC & DESC
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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

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.
  Permalink  
v3
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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

Solution 3

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 ..
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
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web01 | 2.8.170915.1 | Last Updated 16 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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