MySQL – Get row number on select

Take a look at this.

Change your query to:

SET @rank=0;
SELECT @rank:=@rank+1 AS rank, itemID, COUNT(*) as ordercount
  FROM orders
  GROUP BY itemID
  ORDER BY ordercount DESC;
SELECT @rank;

The last select is your count.

Related Posts:

  1. ROW_NUMBER() in MySQ
  2. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  3. Unknown column in ‘field list’ error on MySQL Update query
  4. Unknown column in ‘field list’ error on MySQL Update query
  5. MySQL – Operand should contain 1 column(s)
  6. MySQL “CREATE TABLE IF NOT EXISTS” -> Error 1050
  7. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  8. MySQL create table if not exists and insert record only if table was created
  9. How do I use properly CASE..WHEN in MySQL
  10. How can I do a FULL OUTER JOIN in MySQL?
  11. MySQL query String contains
  12. What’s the difference between VARCHAR and CHAR?
  13. Case statement in MySQL
  14. How to declare a variable in MySQL?
  15. MySQL Cannot Add Foreign Key Constraint
  16. How to split the name string in mysql?
  17. How do I import an SQL file using the command line in MySQL?
  18. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  19. mysql Foreign key constraint is incorrectly formed error
  20. MySQL: Invalid use of group function
  21. How can I return pivot table output in MySQL?
  22. Compare dates in MySQL
  23. MySQL “WITH” clause
  24. MySQL Multiple Joins in one query?
  25. Error Code: 2013. Lost connection to MySQL server during query
  26. MySQL syntax for Join Update
  27. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  28. how to drop partition without dropping data in MySQL?
  29. MySQL combine two columns into one column
  30. Simple way to calculate median with MySQL
  31. Error 1046 No database Selected, how to resolve?
  32. MySQL Error 1264: out of range value for column
  33. Remove duplicate rows in MySQL
  34. How to SUM and SUBTRACT using SQL?
  35. Cast from VARCHAR to INT – MySQL
  36. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  37. How do you force mysql LIKE to be case sensitive?
  38. MySQL – UPDATE multiple rows with different values in one query
  39. How to create a MySQL hierarchical recursive query?
  40. ERROR 1148: The used command is not allowed with this MySQL version
  41. Insert into a MySQL table or update if exists
  42. How to round down to nearest integer in MySQL?
  43. MySQL starts with searching issue
  44. Query to convert from datetime to date mysql
  45. Is it possible to GROUP BY multiple columns using MySQL?
  46. MySQL error: key specification without a key length
  47. Equivalent of Oracle’s RowID in SQL Server
  48. Best way to test if a row exists in a MySQL table
  49. Check if MySQL table exists without using “select from” syntax?
  50. MySQL equivalent of DECODE function in Oracle
  51. mysql update column with value from another table
  52. The used SELECT statements have a different number of columns
  53. How to delete from multiple tables in MySQL?
  54. MySQL: Can’t create table (errno: 150)
  55. How to copy a row and insert in same table with a autoincrement field in MySQL?
  56. Join vs. sub-query
  57. Alternative to except in MySQL
  58. Equivalent of explode() to work with strings in MySQL
  59. MySQL OPTIMIZE all tables?
  60. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  61. MySQL error: Unknown column in ‘where clause’
  62. How to truncate the text returned for a column in a MySQL query
  63. Using union and order by clause in mysql
  64. MySQL LIKE IN()?
  65. SQL WITH clause example [duplicate]
  66. SQL query to select dates between two dates
  67. Must declare the scalar variable
  68. SQL join on multiple columns in same tables
  69. SQL Switch/Case in ‘where’ clause
  70. Getting “Lock wait timeout exceeded; try restarting transaction” even though I’m not using a transaction
  71. How to run a SQL query on an Excel table?
  72. The backend version is not supported to design database diagrams or tables
  73. Create mysql table directly from CSV file using the CSV Storage engine?
  74. Determine ROW that caused “unexpected end of file” error in BULK INSERT?
  75. Difference between View and table in sql
  76. Exclude a column using SELECT * [except columnA] FROM tableA?
  77. Convert partially non-numeric text into number in MySQL query
  78. Difference between INNER JOIN and LEFT SEMI JOIN
  79. PostgreSQL visual interface similar to phpMyAdmin?
  80. Transaction when using WP functions rather than vanilla SQL?
  81. How To Write An Inner Join With WP Query
  82. Check if image exists before uploading with media_sideload_image()
  83. SQL query to get adjacent posts from search query
  84. How to import a Typo3 database to a wordpress site?
  85. Code to remove authors with no posts connected to them
  86. SQL Query in WordPress – Getting Impossible WHERE error
  87. Using AND and bracket grouping in SQL not working
  88. Clean up very big and very dirty database
  89. How can I convert everything from category X to have post format Link
  90. SQL query injection with fifu image
  91. How to get database connection details without longing to cpanel in WordPress?
  92. SQL query to change custom field in WordPress database
  93. Reset post IDs with all post meta
  94. How to Add or Change Post Title
  95. Why does DROP TABLE-ing the `wp_options` reset my user session?
  96. How to refactor DB queries for better TTFB in WordPress?
  97. query sql-table and change entities
  98. Backtick (MySQL norm) added to SQL Server Query causing error
  99. SQL Query Search page
  100. finding out the top 5 source ( source is a custom taxonomy ), in a given category

Leave a Comment