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 (
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