SQL Server Ordering row number sequentially for columns that have same values

v2g6jxz6 于 12个月前发布在其他

关注(0)|答案(2)|浏览(113)

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.

sql-server

来源：https://stackoverflow.com/questions/77621445/ordering-row-number-sequentially-for-columns-that-have-same-values

2条答案

按热度按时间

yhxst69z1#

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

赞(0）回复(0）举报 12个月前

rekjcdws2#

Consider this:

DECLARE @Table TABLE (No INT, ReferenceNumber INT, TransactionTime TIME, Amount INT,    Source VARCHAR(1));
INSERT INTO @Table (No, ReferenceNumber, TransactionTime, Amount, Source) VALUES
(1, 3099, NULL   , -1125, 'A'),
(2, 3099, NULL   , -1125, 'B'),
(3, 3100, NULL   , -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');

SELECT *, ROW_NUMBER() OVER(ORDER BY ReferenceNumber, TransactionTime, Source)
  FROM @Table;

Here we've just added some addition columns to the ROW_NUMBERORDER .

1	3099		-1125	A	1
2	3099		-1125	B	2
3	3100		-375	B	3
4	3101	05:00:00.0000000	-375	A	4
5	3101	05:00:00.0000000	2000	B	5
6	3101	06:00:00.0000000	2000	A	6
7	3102	06:00:00.0000000	2500	B	7

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Ordering row number sequentially for columns that have same values

2条答案

相关问题

热门标签

最新问答