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

User · Answer

select ROW NUMBER   over  order by pk field   as srno  from TableName

User · Answer

I m not certain  based on your question if you want numbered rows that will remember their numbers even if the underlying data changes  and gives a different ordering   but if you just want numbered rows - that reset on a change in customer ID  then try using the Partition by clause of row number    row number   over partition by CustomerID order by CustomerID

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Using Common Table Expression  CTE    WITH CTE AS      SELECT ROW NUMBER   OVER ORDER BY CustomerId  AS RowNumber      Customers       FROM Customers   SELECT   FROM CTE

User · Answer

select    ROW NUMBER   Over  Order by CustomerID  As  S N       CustomerID      CustomerName     Address     City     State     ZipCode   from Customers

User · Answer

SELECT ROW NUMBER    OVER  ORDER BY  ColumnName1  As SrNo  ColumnName1   ColumnName2 FROM  TableName

User · Answer

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

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

Examples related to sql-server