Consider:
select row_number()
over (partition by product_category order by price desc) ARank,*
from Product
Now: instead of using an analytic function such as _row_number()_ or rank() - and without using correlated subqueries is there a way to obtain the same results in standard sql?
Note: there is an excellent Q&A on how to emulate the analytic functions : Implement Rank without using analytic function. However all of the answers use correlated subqueries .
The motivation is the following: I am using a SQL based repository for which analytic functions are not supported except over timestamp columns and for which correlated subqueries are not supported at all: in particular spark structured streaming . Other standard sql constructs and functions are supported: so the question is about how to obtain these results with the remaining/simpler constructs (if it's actually possible at all).
You can do this by joining the table to itself, with the condition that the price in the joined table is less than the price in the original, and then counting the number of rows in the joined table to give an ordering. For example:
SELECT p1.product_category, p1.name, p1.price, COUNT(*) AS row_number
FROM Product p1
JOIN Product p2 ON p2.product_category = p1.product_category AND p2.price <= p1.price
GROUP BY p1.product_category, p1.price, p1.name
ORDER BY p1.product_category, row_number
Note that if only_full_group_by mode is enabled you need to include all fields in the GROUP BY clause as are in the SELECT (as I have done for this example query).
Output (for my demo on dbfiddle):
product_category name price row_number
clothing tie 20.00 1
clothing shirt 35.99 2
clothing coat 55.20 3
furniture chair 20.50 1
furniture table 108.00 2
furniture sofa 134.00 3
furniture bed 220.00 4