Increment value from 1 to onward based on other field in SQL-CodePudding

id	result
KLLR	1
KLLR	2
KLLR	3
KLLR	4
OLRQ	1
OLRQ	2
PKRD	1
PKRD	2
PKRD	3

SQL increment value from 1 to onward based on other fields and the output calculated field is the result. Please view the image for more clarity. And below is my SQL which I tried. I want the result in another calculated field as you can see in the image.

SELECT id, SUM(COUNT(id)) AS result
FROM t1 
GROUP BY id

CodePudding user response：

A simple row number should work for you here:

SELECT id,
       ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS Result
FROM t1;

You tagged both MySQL and SQL Server. Do note that they are completely different products but the same should work in both. MySQL introduced ROW_NUMBER() in 8.0.

Toward HansG's comment,

SELECT id,
       ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT NULL)) AS Result
FROM t1;

will give you the random ordering mentioned.