ROW NUMBER() OVER

Question

I came across a somewhat special syntax, could you help in figuring out what it means? Thank you.

SELECT ROW NUMBER() OVER (ORDER BY Product.ProductID) FROM Product;

Also, this fails. I'm particularly interested in the ROW NUMBER() OVER bit. It's the first time I've encountered the OVER keyword, too.

Please let me know if you need the full example. I shortened it a bit for the sake of clarity.

Answer 1

The ROW_NUMBER() function requires the OVER(ORDER BY) expression to determine the order that the rows are numbered. The default order is ascending but descending can also be used. This function is useful for a variety of things like keeping track of rows when iterating through a set of records since T-SQL does not allow a cursor to be retrieved outside of T-SQL. @tunimise fasipe is correct, you are missing the _

Answer 2

Note that your are missing the underscore in ROW_NUMBER

SELECT ROW_NUMBER() OVER (ORDER BY Products.ProductID) FROM Products;

What it does is that it prints out the row number of each record in the products table in the order they were retrieved (as ordered by ProductID)

e.g.
RowNumber   ProductName
------------------------
1           Flower
2           Bag
3           Car
...         ...

I added the ProductName column for clarity