sqlsql-servert-sqlsql-order-by

Ordering row number sequentially for columns that have same values


I have a table like this:

No. ReferenceNumber TransactionTime Amount Source
1 3099 N/A -1125 A
2 3099 N/A -1125 B
3 3100 N/A -375 B
4 3101 05:00 -375 A
5 3101 05:00 2000 B
6 3101 06:00 2000 A
7 3102 06:00 2500 B

How to determine a number (in the No. column) sequentially for the above data using SQL Server? I appreciate for the help.

I've tried this T-SQL code:

SELECT 
    ROW_NUMBER() OVER (ORDER BY ReferenceNumber asc) No., 
    ReferenceNumber, TransactionTime, Amount, Source
FROM 
    aTable 

I got the result like this:

No. ReferenceNumber TransactionTime Amount Source
1 3099 N/A -1125 A
2 3099 N/A -375 B
3 3100 N/A -1125 B
4 3101 05:00 -375 A
5 3101 05:00 2000 B
6 3101 06:00 2500 B
7 3102 06:00 2000 A

But, it's not the expectation like the above table.


Solution

  • Consider this approach

    CREATE TABLE #Transactions([No.] int identity(1, 1), ReferenceNumber INT
      ,TransactionTime Varchar(15), Amount int, Source Varchar(1))    ;
    
    INSERT into #Transactions
    SELECT ReferenceNumber, TransactionTime, Amount, Source
    FROM aTable;
    
    SELECT * FROM #Transactions