ROW_NUMBER() rules!

Despite working with DW systems for some while, the use of Oracle Analytic functions on a production system seems to have passed me by. Not because they don’t work or are slow – far from it, but because the business questions I usually get asked to look at typically can be solved with ‘more traditional’ SQL.

Today a customer asked us to help write a ‘price list’ for their stores based on the products NOT stocked at each store, a sort of ‘special order’ pricelist. The problem for the customer was that price was not an attribute of product but of a lower level member of the product hierarchy, the stock unit. These stock units could represent differing pack sizes and perhaps price-marked and offer-marked stock. The customer’s business rule was to select the price of the stock unit that had the highest price for the smallest pack size for any given product (and no, this was not to maximize income!)

To my mind this seemed a great use of analytic functions and in this case ROW_NUMBER. ROW_NUMBER() like ROWNUM enumerates the rows in a result set, however unlike rownum the count resets to 1 on each change of partition key. By ordering the result set in each partition we can fulfil the customer’s business requirement and force the rows that we are interested in to have a ROW_NUMBER() of 1. An example query would be:

SELECT PRODUCT_ID, PRICE from
(
SELECT P.PRODUCT_ID, S.PRICE,
ROW_NUMBER() over (PARTITITION BY P.PRODUCT_ID
ORDER BY S.PACK_SIZE, S.PRICE DESC) RN
FROM
PRODUCT P, STOCK_UNIT S
WHERE P.PRODUCT_ID = S.PRODUCT_ID
)
WHERE RN = 1;

This code is amazingly fast to run an a lot easier to write than more conventional SQL. As someone said “Analytics Rock”