[sql-server] Generate Row Serial Numbers in SQL Query

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

ALTER function dbo.FN_ReturnNumberRows(@Start int, @End int) returns @Numbers table (Number int) as
begin
    insert into @Numbers
    select n = ROW_NUMBER() OVER (ORDER BY n)+@Start-1 from (
    select top (@End-@Start+1) 1 as n from information_schema.columns as A
        cross join information_schema.columns as B
        cross join information_schema.columns as C
        cross join information_schema.columns as D
        cross join information_schema.columns as E) X
    return
end
GO

select * from dbo.FN_ReturnNumberRows(10,9999)

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.

Implementing Serial Numbers Without Ordering Any of the Columns

Demo SQL Script-

IF OBJECT_ID('Tempdb..#TestTable') IS NOT NULL
    DROP TABLE #TestTable;

CREATE TABLE #TestTable (Names VARCHAR(75), Random_No INT); 

INSERT INTO #TestTable (Names,Random_No) VALUES
 ('Animal', 363)
,('Bat', 847)
,('Cat', 655)
,('Duet', 356)
,('Eagle', 136)
,('Frog', 784)
,('Ginger', 690); 

SELECT * FROM #TestTable;

There are ‘N’ methods for implementing Serial Numbers in SQL Server. Hereby, We have mentioned the Simple Row_Number Function to generate Serial Numbers.

ROW_NUMBER() Function is one of the Window Functions that numbers all rows sequentially (for example 1, 2, 3, …) It is a temporary value that will be calculated when the query is run. It must have an OVER Clause with ORDER BY. So, we cannot able to omit Order By Clause Simply. But we can use like below-

SQL Script

IF OBJECT_ID('Tempdb..#TestTable') IS NOT NULL
    DROP TABLE #TestTable;

CREATE TABLE #TestTable (Names VARCHAR(75), Random_No INT); 

INSERT INTO #TestTable (Names,Random_No) VALUES
 ('Animal', 363)
,('Bat', 847)
,('Cat', 655)
,('Duet', 356)
,('Eagle', 136)
,('Frog', 784)
,('Ginger', 690); 

SELECT Names,Random_No,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SERIAL_NO FROM #TestTable;

In the Above Query, We can Also Use SELECT 1, SELECT ‘ABC’, SELECT ” Instead of SELECT NULL. The result would be Same.

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

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

I found one solution for MYSQL its easy to add new column for SrNo or kind of tepropery auto increment column by following this query:

SELECT @ab:=@ab+1 as SrNo, tablename.* FROM tablename, (SELECT @ab:= 0)
AS ab

Using Common Table Expression (CTE)

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

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