Is it possible to add a identity column to a GROUP BY so that each duplicate has a identity number?
My original data looks like this:
1 AAA [timestamp]
2 AAA [timestamp]
3 BBB [timestamp]
4 CCC [timestamp]
5 CCC [timestamp]
6 CCC [timestamp]
7 DDD [timestamp]
8 DDD [timestamp]
9 EEE [timestamp]
....
And I want to convert it to:
1 AAA 1
2 AAA 2
4 CCC 1
5 CCC 2
6 CCC 3
7 DDD 1
8 DDD 2
...
The solution was:
CREATE PROCEDURE [dbo].[RankIt]
AS
BEGIN
SET NOCOUNT ON;
SELECT *, RANK() OVER(PARTITION BY col2 ORDER BY timestamp DESC) AS ranking
FROM MYTABLE;
END
There is no straightforward way to add IDENTITY to an existing column.
Once we insert a row in a table, the @@IDENTITY function column gives the IDENTITY value generated by the statement. If we run any query that did not generate IDENTITY values, we get NULL value in the output. The SQL @@IDENTITY runs under the scope of the current session.
You could try using ROW_NUMBER if you are using Sql Server 2005
DECLARE @Table TABLE(
ID INT,
Val VARCHAR(10)
)
INSERT INTO @Table SELECT 1,'AAA'
INSERT INTO @Table SELECT 2,'AAA'
INSERT INTO @Table SELECT 3,'BBB'
INSERT INTO @Table SELECT 4,'CCC'
INSERT INTO @Table SELECT 5,'CCC'
INSERT INTO @Table SELECT 6,'CCC'
INSERT INTO @Table SELECT 7,'DDD'
INSERT INTO @Table SELECT 8,'DDD'
INSERT INTO @Table SELECT 9,'EEE'
SELECT *,
ROW_NUMBER() OVER(PARTITION BY VAL ORDER BY Val)
FROM @Table
create table #testalot
(
[id] int identity,
data varchar(50)
)
insert #testalot (data) values('AAA')
insert #testalot (data) values('AAA')
insert #testalot (data) values('BBB')
insert #testalot (data) values('CCC')
insert #testalot (data) values('CCC')
insert #testalot (data) values('CCC')
insert #testalot (data) values('DDD')
insert #testalot (data) values('DDD')
select *,ROW_NUMBER() OVER(PARTITION BY data ORDER BY data DESC) AS 'Number'
from #testalot
drop table #testalot
returns
id data Number
1 AAA 1
2 AAA 2
3 BBB 1
4 CCC 1
5 CCC 2
6 CCC 3
7 DDD 1
8 DDD 2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With