1

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

WestCoastProjects
  • 58,982
  • 91
  • 316
  • 560
  • Take a look at @MostyMostacho answer to this question https://stackoverflow.com/questions/1895110/row-number-in-mysql – Nick Apr 21 '19 at 00:01
  • @Nick That appears to meet my criteria shown in the question. But upon seeing they use a _semi-join_ .. i'm guessing I under-specified my problem (yet _another_ type of join not supported..) . In any case feel free to make an answer - since that appears to meet the given specifications . – WestCoastProjects Apr 21 '19 at 01:24
  • It's probably better to edit your question to refine your specification to exclude that possibility as well. No point giving an answer that isn't really what you want. – Nick Apr 21 '19 at 01:38
  • I believe you have found the answer requiring the least moving parts: what I actually need would not exist: go ahead and make it an answer. In the meantime I created an answer with the content of that link – WestCoastProjects Apr 21 '19 at 01:43

1 Answers1

4

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
Nick
  • 138,499
  • 22
  • 57
  • 95