Generate serial numbers in SQL Query

Question

I have a client transaction table. I need to create a query that contains a pseudo-column of a serial number. The serial number should be automatically reset and begin with 1 after changing the client ID.

Now I am familiar with the row_number () function in SQL. This does not exactly solve my problem, because as far as I know, the serial number will not be reset if the line order is changed.

I want to do this in one query (SQL Server) and without the need to use any temporary use of tables, etc.

Any ideas?

Thank you in advance

Answer 1

I'm not sure, based on your question, if you want numbered lines to remember their numbers, even if the underlying data changes (and give a different order), but if you just want numbered lines, it is reset on changing the client ID, then try using row split section row_number ()

row_number() over(partition by CustomerID order by CustomerID) 

Answer 2

Sometimes we may not want to apply the order in our result set to add a serial number. But if we use ROW_NUMBER() , then we should have an ORDER BY . Thus, for this we can simply apply tricks to avoid ordering in the result set.

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

To do this, we do not need to apply order on our result set. We will simply add ItemNo to our specified result set.

Answer 3

 select ROW_NUMBER() Over (Order by CustomerID) As [SN], CustomerID , CustomerName, Address, City, State, ZipCode from Customers; 

Answer 4

 select ROW_NUMBER() over (order by pk_field ) as srno from TableName 

Answer 5

 SELECT ROW_NUMBER() OVER (ORDER BY ColumnName1) As SrNo, ColumnName1, ColumnName2 FROM TableName 

Answer 6

Using a common table expression (CTE)

 WITH CTE AS( SELECT ROW_NUMBER() OVER(ORDER BY CustomerId) AS RowNumber, Customers.* FROM Customers ) SELECT * FROM CTE