I have a table like this:
+------+-----------+
|caseID|groupVarian|
+------+-----------+
|1 |A,B,C,D,E |
+------+-----------+
|2 |A,B,N,O,P |
+------+-----------+
|3 |A,B,N,O,P |
+------+-----------+
|4 |A,B,C,D,F |
+------+-----------+
|5 |A,B,C,D,E |
+------+-----------+
I would like to get a new column nameVarian
, such that same groupVarian
values have same ranking represented by nameVarian
(eg: v1, v2 and so on). However, nameVarian
values assigned to a specific groupVarian
should be as per the order of caseID
(in the order they appear inside the table).
The output should be something like:
+------+-----------+----------+
|caseID|groupVarian|namevarian
+------+-----------+----------+
|1 |A,B,C,D,E |v1 |
+------+-----------+----------+
|2 |A,B,N,O,P |v2 |
+------+-----------+----------+
|3 |A,B,N,O,P |v2 |
+------+-----------+----------+
|4 |A,B,C,D,F |v3 |
+------+-----------+----------+
|5 |A,B,C,D,E |v1 |
+------+-----------+----------+
For MySQL version < 8.0 (OP's version is 5.6):
The problem statement looks like needing DENSE_RANK functionality over groupVarian
; however it is not. As explained by @Gordon Linoff:
You appear to want them enumerated by the order they appear in the data.
Assuming that your table name is t
(please change the table and field name(s) accordingly for your code). Here is an approach utilizing session variables (for older versions of MySQL), giving the desired result (DB Fiddle):
SET @row_number = 0;
SELECT t3.caseID,
t3.groupVarian,
CONCAT('v', t2.num) AS nameVarian
FROM
(
SELECT
(@row_number:=@row_number + 1) AS num,
t1.groupVarian
FROM
(
SELECT DISTINCT groupVarian
FROM t
ORDER BY caseID ASC
) AS t1
) AS t2
INNER JOIN t AS t3
ON t3.groupVarian = t2.groupVarian
ORDER BY t3.caseID ASC
Additionally: My earlier attempt to emulate DENSE_RANK
functionality, works well. Although previous query can also be tweaked slightly to achieve DENSE_RANK
functionality. However, the following query is more efficient, as it creates lesser Derived tables, and avoids JOIN on groupVarian
:
SET @row_number = 1;
SET @group_varian = '';
SELECT inner_nest.caseID,
inner_nest.groupVarian,
CONCAT('v', inner_nest.num) as nameVarian
FROM (
SELECT
caseID,
@row_number:=CASE
WHEN @group_varian = groupVarian THEN @row_number
ELSE @row_number + 1
END AS num,
@group_varian:=groupVarian as groupVarian
FROM
t
ORDER BY groupVarian
) AS inner_nest
ORDER BY inner_nest.caseID ASC
You could use DENSE_RANK
(MySQL 8.0):
SELECT *, CONCAT('v', DENSE_RANK() OVER(ORDER BY groupVarian)) AS namevarian
FROM tab
ORDER BY CaseID;
db<>fiddle demo
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