Generate Row Serial Numbers in SQL Query

Question

I have a customer transaction table. I need to create a query that includes a serial number pseudo column. The serial number should be automatically reset and start over from 1 upon change in customer ID.

Now, I am familiar with the row_number() function in SQL. This doesnt exactly solve my problem because to the best of my knowledge the serial number will not be reset in case the order of the rows change.

I want to do this in a single query (SQL Server) and without having to go through any temporary table usage etc.

Any ideas?

Thanks in advance

Answer 1

Sometime we might don't want to apply ordering on our result set to add serial number. But if we are going to use ROW_NUMBER() then we have to have a ORDER BY clause. So, for that we can simply apply a tricks to avoid any ordering on the result set.

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS ItemNo, ItemName FROM ItemMastetr

For that we don't need to apply order by on our result set. We'll just add ItemNo on our given result set.