MySQL 5.6 – DENSE_RANK like functionality without Order By

by

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

More Related Contents:

  1. SQL Query needed for fetch value from Two identical table but not fully identical table
  2. How to create a MySQL hierarchical recursive query?
  3. Error related to only_full_group_by when executing a query in MySql
  4. SQL split values to multiple rows
  5. How to reset AUTO_INCREMENT in MySQL
  6. How to declare a variable in MySQL?
  7. MySQL Query GROUP BY day / month / year
  8. MySQL Cannot Add Foreign Key Constraint
  9. Grouping into interval of 5 minutes within a time range
  10. SQL counting all rows instead of counting individual rows
  11. Convert timestamp to date in MySQL query
  12. Update a column value, replacing part of a string
  13. MySQL Like multiple values
  14. In MySQL, should I quote numbers or not?
  15. MySQL add days to a date
  16. mysql dynamic query in stored procedure
  17. Check for x consecutive days – given timestamps in database
  18. MySQL Insert Into from one Database in another
  19. How to alter a column and change the default value?
  20. MySQL multiple columns in IN clause
  21. How to select one row randomly taking into account a weight?
  22. GROUP BY – do not group NULL
  23. Composite key as foreign key (sql)
  24. MySQL – Replace Character in Columns
  25. Sort the rows according to the order specified in WHERE IN clause
  26. update a column by subtracting a value
  27. How can I use SQL’s YEAR(), MONTH() and DAY() in Doctrine2?
  28. Optimistic locking in MySQL
  29. Insert and set value with max()+1 problems
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment