Having this:

BEGIN TRANSACTION;

CREATE TABLE MY_TABLE(Salary integer, Name text, Country text);

INSERT INTO MY_TABLE VALUES(10,'Tom', 'Albania');
INSERT INTO MY_TABLE VALUES(20,'Lucy', 'Bulgaria');
INSERT INTO MY_TABLE VALUES(30,'Frank', 'Bulgaria');
INSERT INTO MY_TABLE VALUES(30,'Jane', 'Canada' );
INSERT INTO MY_TABLE VALUES(50,'Robert', 'Canada');
INSERT INTO MY_TABLE VALUES(50,'Robert2', 'Canada');
INSERT INTO MY_TABLE VALUES(100,'Robert2', 'Canada');
INSERT INTO MY_TABLE VALUES(50,'Robert3', 'Canada');
INSERT INTO MY_TABLE VALUES(60,'Robert6', 'Denmark');

COMMIT;

SELECT Salary, Name, Country,Salary || Country AS 'New' FROM MY_TABLE
WHERE Salary < 100

Producing this:

The question: Instead of the value in New, I want to have a subsequent number, that is the same for all equal strings. E.g., something like this:

The number 5 is the same, because 50Canada is repeated 3 times in the table above.

CodePudding user response：

You can try to use dense_rank window function.

SELECT *,dense_rank() OVER(ORDER BY Salary,Country) new
FROM MY_TABLE
WHERE Salary < 100