Easiest Approach to Selecting Top Result from Windowed Function?

Question

So, let's say I have a list of customers and I want to select details for all customers, as well as their most purchased product from a specific class of products. Even if they have not purchased one of these products I want to select the customer detail, while simply displaying null for their most purchased product from that class.

I would start with the following either as a CTE or temp table:

SELECT
 CUST_NUMBER
,PRODUCT
,ROW_NUMBER() OVER (PARTITION BY CUST_NUMBER ORDER BY COUNT(ORDER_NUM) DESC) [ProdRank]
FROM ORDERS
WHERE PROD_CLASS = 'x'
GROUP BY 
 CUST_NUMBER
,PRODUCT

The thing is this - There can be many different products within this product class, and I am only interested in selecting where ProdRank = 1. As you might know though, I cannot specify either in the WHERE or in HAVING clause for ProdRank to = 1.

I get the error message "Windowed functions can only appear in the SELECT or ORDER BY clauses."

The situation is further complicated by the fact that many customers may have not ordered any products within the product class. Because of this I cannot simply left join the customer list to the above and specify WHERE ProdRank = 1, or else it mimics an inner join and I drop any customers where ProdRank is Null.

The method I've come up with in order to deal with this is to first create a temp table with the code above as #Products which includes the customer and every product with the respective ranking. I then create a second temp table called #TopProducts where I simply :

SELECT * FROM 
#Products WHERE 
ProdRank = 1

After that I just left join against #TopProducts from my Customers table.

It seems like there should be a simpler way of dealing with this though. Is there any way I can select the top partitioned result of ROW_NUMBER() or RANK() in one step, rather than creating two temp tables?

Anon · Answer 1 · 2015-04-21T15:51:48.403

2

Use a Common Table Expression

WITH topProducts AS (
  SELECT
   CUST_NUMBER
  ,PRODUCT
  ,ROW_NUMBER() OVER (PARTITION BY CUST_NUMBER ORDER BY COUNT(ORDER_NUM) DESC) [ProdRank]
  FROM ORDERS
  WHERE PROD_CLASS = 'x'
  GROUP BY 
   CUST_NUMBER
  ,PRODUCT
)
SELECT *
FROM CustomerDetails c
LEFT JOIN TopProducts p ON (ProdRank = 1 AND c.CUST_NUMBER = p.CUST_NUMBER)

Use a subquery:

SELECT *
FROM CustomerDetails c
LEFT JOIN (
  SELECT
   CUST_NUMBER
  ,PRODUCT
  ,ROW_NUMBER() OVER (PARTITION BY CUST_NUMBER ORDER BY COUNT(ORDER_NUM) DESC) [ProdRank]
  FROM ORDERS
  WHERE PROD_CLASS = 'x'
  GROUP BY 
   CUST_NUMBER
  ,PRODUCT
) p ON (ProdRank = 1 AND c.CUST_NUMBER = p.CUST_NUMBER)

edited Apr 21 '15 at 15:51

answered Apr 20 '15 at 19:00

Anon

10,660
1
29
31

I do still want to pick up customers from my customer table who do not appear in myCTE. If I left join against myCTE WHERE ProdRank = 1 I exclude all customers that did not order a product within the selected product class. It does save a step, but wouldn't I still have to create a temp table where I select * from myCTE WHERE ProdRank = 1, then left join against that? Is there a way to do this in one step? – SELECTCOUNTSTAR Apr 20 '15 at 19:18
1

Just move the `ProdRank = 1` predicate to the `ON()` clause of the `LEFT JOIN`. This will prevent the rows on the left side being filtered out even if there is nothing on the right side that matches. – Anon Apr 21 '15 at 15:54

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

I would use outer apply and top in your scenario. Does that make sense? Few examples here Real life example, when to use OUTER / CROSS APPLY in SQL

I would write a piece of code, but I'm on mobile and that's really not comfortable...

edited May 23 '17 at 11:50

Community

1
1

answered Apr 20 '15 at 19:59

Evaldas Buinauskas

13,739
11
55
107

Easiest Approach to Selecting Top Result from Windowed Function?

2 Answers2