CODEWITHSUNDEEP
mysqlsqldense-rank
Intact Abode
Intact Abode

Reputation: 392

Generate row numbers and assign same row number to same values

I have a requirement to show duplicate row number for duplicate values for every new occurrence.

Sample Table orders with column AGENT_CODE and i want an expected output to be like

Sl  agent_code
------------------
1   A001
2   A002
2   A002
2   A002
2   A002
2   A002
2   A002
2   A002
3   A003
3   A003
4   A004
4   A004
4   A004
4   A004
5   A007
5   A007
5   A007
6   A008
6   A008

I was able to get the row number with a sequence like 1,2,3,4....19 by writing the query:

SET @row_number := 0;
SELECT @row_number := @row_number + 1 AS row_number, AGENT_CODE
FROM orders, (SELECT @row_number := 0) t
ORDER BY agent_code

But what I wanted is the row number must be printed in a sequence for every new occurrence as shown in the expected output.

Upvotes: 2

Views: 3252

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272236

You want something similar to DENSE_RANK() OVER (ORDER BY AGENT_CODE) which requires MySQL 8+. In older versions you could simply do this:

SELECT AGENT_CODE, (
    SELECT COUNT(DISTINCT AGENT_CODE) + 1
    FROM orders AS x
    WHERE x.AGENT_CODE < t.AGENT_CODE
) AS sn
FROM orders AS t

PS: if you want to use user variables then you need another variable called @prevagent.

Upvotes: 4

Related Questions