sql row_number() vs select row_number() to get data

Question

i have the following table

Prod_id   Units sold
 1,       100 
 2,       95 
 3,       84 
 4,       95 
 5,       100

I want to know why the first query gives proper row counts while the 2nd one returns only 1's

select 
 (ROW_NUMBER() OVER (Partition by amount order by id)) 
from Products 


select *, 
(select ROW_NUMBER() OVER (Partition by amount order by id) a )  
from Products

Results are here: http://sqlfiddle.com/#!6/dfd59/11

I want to use this to apply 100, 100 1st and 5th rows and 95, 95 to the 2nd and 4th rows within this query without using a with statement. Please let me know if there is a way

`select *, ROW_NUMBER() OVER (Partition by amount order by id) a from Products ` — Jacob H, Jan 19 '18 at 20:58
Use your first query, take the row_Number window function out of those parenthesis, they do nothing but add noise. Then add the other columns you want. — Sean Lange, Jan 19 '18 at 20:59
In your first query, Row_Number() is evaluated as an expression; in your second it is part of a subquery that gets executed once per each row in Products. Since the subquery only has one row each time it's executed, it will always return 1 — ColdSolstice, Jan 19 '18 at 21:06
I'm upvoting because I clearly have not had to explain what happens in this situation, so you have an interesting question. I hope my explanation makes some sense. — Gordon Linoff, Jan 19 '18 at 21:09

Gordon Linoff · Accepted Answer · 2018-01-19T21:09:47.507

Writing a subquery without a from clause is a waste of a subquery. It doesn't do anything desirable.

Just call the function directly.

Note: This advice applies to any expression in a subquery, not only row_number().

Why does your subquery return only "1"s? That reason is rather subtle to explain. But, imagine that the subquery were written as:

select *, 
       (select ROW_NUMBER() OVER (Partition by amount order by id) a
        from dual)

This is, in fact, how the query could or would be written in several SQL databases. dual is a table with exactly one row. That little fact emphasizes what is happening. The subquery is referring to one row at a time. Hence, the row_number() that you are getting refers only to partitioning by that row. Voila! You only get "1".

I realize that it might help if you considered the subquery as:

select *, 
       (select ROW_NUMBER() OVER (Partition by amount order by id) a
        from (select products.amount, products.id) p
       )

This emphasizes that the row_number() is being applied over the result set of the sub-select. That sub-select -- by definition -- has only one row, which results in the "1" being returned.

sql row_number() vs select row_number() to get data

1 Answers1