CODEWITHSUNDEEP
sqlsql-server
user1839815
user1839815

Reputation: 21

SQL Query to get a unique seqence

I have following data:

Txn Nmbr    Item ID     Desc
4           1111        Test 1
6           2222        Test 2
6           3333        Test 3
7           4444        Test 4
7           5555        Test 5
7           6666        Test 6

I want all above data with ONE MORE COLUMN which is unique sequence number for each 'Txn Nmbr'. So, output required is,

Txn Nmbr    Item ID     Desc        Unique Txn
4           1111        Test 1      1
6           2222        Test 2      2
6           3333        Test 3      2
7           4444        Test 4      3
7           5555        Test 5      3
7           6666        Test 6      3

Please help me! Thanks in advance!

Upvotes: 2

Views: 81

Answers (4)

Justin
Justin

Reputation: 9724

Query:

SQLFiddleExample

SELECT
t1.*,
(SELECT COUNT(*)
 FROM tbl t2
 WHERE t1.[Txn Nmbr] = t2.[Txn Nmbr]) as [Unique Txn]
FROM tbl t1

Result:

| TXN NMBR | ITEM ID |   DESC | UNIQUE TXN |
--------------------------------------------
|        4 |    1111 | Test 1 |          1 |
|        6 |    2222 | Test 2 |          2 |
|        6 |    3333 | Test 3 |          2 |
|        7 |    4444 | Test 4 |          3 |
|        7 |    5555 | Test 5 |          3 |
|        7 |    6666 | Test 6 |          3 |

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please try:

DECLARE @table as TABLE(TxnNmbr INT, ItemID INT, Descr NVARCHAR(50))

insert into @table values (4, 1111, 'Test1')
insert into @table values (6, 2222, 'Test2')
insert into @table values (6, 3333, 'Test3')
insert into @table values (7, 4444, 'Test4')
insert into @table values (7, 5555, 'Test5')
insert into @table values (7, 6666, 'Test6')

SELECT
   *,
   DENSE_RANK() OVER (ORDER BY [TxnNmbr]) AS [Unique Txn]
FROM @table

Upvotes: 1

user330315
user330315

Reputation: 

select txn_number, 
       item_id, 
       desc, 
       row_number() over (partition by txn_number order by item_id) as unique_txn
from the_table

Upvotes: 0

Barry Kaye
Barry Kaye

Reputation: 7761

Have a look at TSQL RANK - MSDN link

Upvotes: 0

Related Questions