CODEWITHSUNDEEP
sqlsql-serverrowcountpartition-by
heyNow
heyNow

Reputation: 886

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

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

Upvotes: 2

Views: 1827

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

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.

Upvotes: 2

Related Questions