Saturday, February 22, 2014

How can I use ROW_NUMBER() if the column name is not identified?

I got a question from my friend.

Question: How can I use ROW_NUMBER() if the column name is not identified?
Answer: There is many ways to achieve it practically.

Example1: 
--Normal Behavior Row number generation
SELECT ROW_NUMBER() OVER(ORDER BY @@ROWCOUNT), * FROM myTable

Example2: 
--Normal Behavior Row number generation
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), * FROM myTable

Example3:  
--Random Behavior Row number generation
SELECT ROW_NUMBER() OVER(ORDER BY NEWID()), * FROM myTable

No comments:

Post a Comment