Row No not getting initialized

Question

Hi Row no are not getting initalized on change of Account SELECT ROW_NUMBER() OVER (ORDER BY [G/l Account]) AS RowNumber, [G/l Account], Debit, Credit FROM Masters Thanks

Daniel Wright · Answer

It seems like you are dealing with a scenario where the Row Number is not getting initialized correctly when there's a change in the Account in your SQL query. Usually, the ROW_NUMBER() function is used to assign a unique sequential integer to each row in the result set according to the specified ORDER BY clause. In your SQL query snippet, you are assigning the Row Number using ROW_NUMBER() OVER (ORDER BY [G/l Account]), which indicates that the rows should be numbered based on the [G/l Account] column's values. To troubleshoot the issue where Row Numbers are not getting initialized on the change of Account, you might want to check if the ORDER BY column ([G/l Account] in this case) is appropriately ordering the rows so that the ROW_NUMBER() function can assign the sequential numbers correctly. If the ordering is not as expected, it can lead to unexpected behavior in the row numbering. Additionally, you could consider partitioning the ROW_NUMBER() function by the Account column. This way, the numbering will restart for each unique Account value, ensuring that the Row Numbers are initialized appropriately based on the Account changes. Here is an example of how you can partition the ROW_NUMBER() function by the Account column in your query: SELECT ROW_NUMBER() OVER (PARTITION BY Account ORDER BY [G/l Account]) AS RowNumber, [G/l Account], Debit, Credit FROM Masters; By partitioning the ROW_NUMBER() function by the Account column, you can ensure that the row numbering restarts for each unique Account value, which should address the issue of Row Numbers not getting initialized correctly on the change of Account. I hope this explanation helps you understand and resolve the problem you are facing with Row Numbers not being initialized as expected. If you have any further questions or need more assistance, feel free to ask!

Muhammad Imran Ansari · Answer

Hello Ramco, f the Row Number is not resetting when the Account changes, you need to use the PARTITION BY clause in the ROW_NUMBER() function. This will reset the row numbers for each unique G/L Account . SELECT ROW_NUMBER() OVER (PARTITION BY [G/l Account] ORDER BY [G/l Account]) AS RowNumber, [G/l Account], Debit, Credit FROM Masters; Good Luck!