December 2, 2014 at 7:24 am
I ran the following but only received the top 3 sales.
with cte as (
select SALESMAN_ID,
ORDER_NO,
ORDER_TOTAL,
rank() over (partition by order_no
order by order_no,order_total desc) rnk
from dbo.SYSOENT where ORDER_DATE >= '1/1/14'
)
select top 3 salesman_id,order_no,order_total
from cte
where rnk = 1
order by order_total desc
results:
salesman_idorder_noorder_total
BF 9389037333.86
BF 8630094288.59
BF 8630813491.90
How can I get top 3 sales per salesmanid:
December 2, 2014 at 7:35 am
WITH cte AS
(
SELECT
SALESMAN_ID
,ORDER_NO
,ORDER_TOTAL
,rnk = RANK() OVER (PARTITION BY SALESMAN_ID ORDER BY ORDER_TOTAL DESC)
FROM dbo.SYSOENT
WHERE ORDER_DATE >= '1/1/14'
)
SELECT TOP 3 SALESMAN_ID,ORDER_NO,ORDER_TOTAL
FROM cte
WHERE rnk <= 3
ORDER BY ORDER_TOTAL DESC;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 2, 2014 at 7:48 am
sdh96 (12/2/2014)
How can I get top 3 sales per salesmanid:
;with cte as
(
select SALESMAN_ID, ORDER_NO, ORDER_TOTAL,
Row_Number() over (partition by salesman_Id
order by order_total desc) RowNum
from SYSOENT where ORDER_DATE >= '2014-01-01'
)
select salesman_id,order_no,order_total, RowNum
from cte
where RowNum <= 3
order by SalesMan_ID
Top 3 in the select gives you only the top 3 sales from the first SalesMan_ID, not from each SalesMan_ID.
I don't think Rank is what you want to use. In this case, it works, but I would do more testing that uses data that resembles your actual data.
I left the RowNum from the cte in so you could see that you were really only getting the top 3 sales for verification.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 2, 2014 at 7:55 am
Ol Hand!! Excellent. Exactly!! Much appreciated.
December 2, 2014 at 8:05 am
Glad it worked for you.
I looked in Itzik Ben-Gan's book on Window Functions to get more information about the difference between Row_Number, Rank and Dense_Rank
"When the ordering is unique, both [Dense Rank and Rank] produce the same results as ROW_NUMBER"
In your case, rank would not work if you had 2 or more orders that were the equal from the same Salesman_ID.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 2, 2014 at 8:19 am
That worked great but there are repetative dollar amounts. Can you display how to retrieve the exact same information but three distinct dollar amounts.
December 2, 2014 at 9:05 am
sdh96 (12/2/2014)
That worked great but there are repetative dollar amounts. Can you display how to retrieve the exact same information but three distinct dollar amounts.
I threw this data together based on your desired output so you could see the difference between row_number, rank and dense_rank.
If you un-comment the where clause that compares drank <= 3, then you will get the top 3 orders from each salesman_id, including orders that are identical in amount, which may be a good thing to know!
If you need to filter out the dups, then it will require a bit more work and I don't have the time right this minute to go any deeper. I will look at it tonight if someone else does not come up with something.
declare @sysOEnt table
(
Salesman_ID char(2),
Order_NO int,
Order_Total decimal(11,2)
)
insert @sysOEnt (Salesman_ID, Order_NO, Order_Total)
values
('ZF', 938903, 7333.86)
,('ZF', 863009, 4288.59)
,('ZF', 863081, 3491.90)
,('ZF', 863009, 1288.59)
,('ZF', 863081, 9491.90)
,('BF', 938903, 7333.86)
,('BF', 863009, 4288.59)
,('BF', 863081, 3491.90)
,('BF', 863009, 1288.59)
,('BF', 863081, 9491.90)
,('AF', 938903, 7333.86)
,('AF', 863009, 4288.59)
,('AF', 863081, 3491.90)
,('AF', 863009, 1288.59)
,('AF', 863081, 9491.90)
,('AF', 938903, 7333.86)
,('AF', 863009, 4288.59)
,('AF', 863089, 3491.90)
,('AF', 863008, 1288.59)
,('AF', 863089, 9491.90)
;with cte as
(
select SALESMAN_ID, ORDER_NO, ORDER_TOTAL,
Row_Number() over (partition by salesman_Id
order by order_total desc) RowNum,
Rank() over (partition by salesman_Id
order by order_total desc) RankNum,
Dense_Rank() over (partition by salesman_Id
order by order_total desc) DRank
from @SYSOENT --where ORDER_DATE >= '2014-01-01'
)
select salesman_id,order_no,order_total, RowNum, RankNum, DRank
from cte
--where DRank <= 3
order by SalesMan_ID
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 2, 2014 at 10:27 am
This is a way to get the top 3 unique order amounts for each Salesman_ID. This uses the same data set that I posted previously.
;with cte as
(
select SALESMAN_ID, ORDER_NO, ORDER_TOTAL,
Row_Number() over (partition by salesman_Id
order by order_total desc) RowNum,
Rank() over (partition by salesman_Id
order by order_total desc) RankNum,
Dense_Rank() over (partition by salesman_Id
order by order_total desc) DRank
from @SYSOENT --where ORDER_DATE >= '2014-01-01'
),
cte1 as
(
select SalesMan_ID, Order_No, Order_total, DRank,
Row_Number() over (partition by salesman_ID, order_total
order by order_total) RowNum
from cte
where DRank <= 3
)
select * from cte1
where RowNum = 1
order by Salesman_ID, DRank
I left the Row_Number and Rank functions in the first cte just for demo purposes. They are not needed.
The data is run through the first cte to get the Dense Rank of all orders. The 2nd cte gets all rows that have have a dense_rank <= 3 and then uses row_number to order them by order_total and then finally, the last select gets all the orders with a RowNum = 1.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply