MySQL get row position in ORDER BY

by

Use this:

SELECT x.id, 
       x.position,
       x.name
  FROM (SELECT t.id,
               t.name,
               @rownum := @rownum + 1 AS position
          FROM TABLE t
          JOIN (SELECT @rownum := 0) r
      ORDER BY t.name) x
 WHERE x.name="Beta"

…to get a unique position value. This:

SELECT t.id,
       (SELECT COUNT(*)
          FROM TABLE x
         WHERE x.name <= t.name) AS position,
       t.name    
  FROM TABLE t      
 WHERE t.name="Beta"

…will give ties the same value. IE: If there are two values at second place, they’ll both have a position of 2 when the first query will give a position of 2 to one of them, and 3 to the other…

More Related Contents:

  1. Join vs. sub-query
  2. How do I update if exists, insert if not (AKA “upsert” or “merge”) in MySQL?
  3. How to use GROUP BY to concatenate strings in MySQL?
  4. Natural Sort in MySQL
  5. Best way to test if a row exists in a MySQL table
  6. Comparing date ranges
  7. How do I convert from BLOB to TEXT in MySQL?
  8. Which SQL query is better, MATCH AGAINST or LIKE?
  9. Maintaining order in MySQL “IN” query
  10. MySQL: What is a reverse version of LIKE?
  11. Select TOP X (or bottom) percent for numeric values in MySQL
  12. MySQL select where column is not empty
  13. MySQL: GROUP_CONCAT with LEFT JOIN
  14. Hamming distance on binary strings in SQL
  15. Is it possible to have a default parameter for a mysql stored procedure?
  16. Renaming foreign-key columns in MySQL
  17. How to convert from varbinary to char/varchar in mysql
  18. MySQL GROUP BY behavior
  19. SQL Row_Number() function in Where Clause
  20. How to get rid of “Error 1329: No data – zero rows fetched, selected, or processed”
  21. Update multiple rows with one query?
  22. Two single-column indexes vs one two-column index in MySQL?
  23. VARCHAR as foreign key/primary key in database good or bad?
  24. MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) – Unexpected result
  25. MySql bulk load command line tool
  26. MySQL: Get character-set of database or table or column?
  27. ALTER TABLE without locking the table?
  28. MySQL: Typecasting NULL to 0
  29. How to get the byte size of resultset in an SQL query?
  30. MySQL subquery returns more than one row

Leave a Comment