MySQL equivalent of ORACLES rank()

by

Nothing directly equivalent, but you can fake it with some (not terribly efficient) self-joins. Some sample code from a collection of MySQL query howtos:

SELECT v1.name, v1.votes, COUNT(v2.votes) AS Rank
FROM votes v1
JOIN votes v2 ON v1.votes < v2.votes OR (v1.votes=v2.votes and v1.name = v2.name)
GROUP BY v1.name, v1.votes
ORDER BY v1.votes DESC, v1.name DESC;
+-------+-------+------+
| name  | votes | Rank |
+-------+-------+------+
| Green |    50 |    1 |
| Black |    40 |    2 |
| White |    20 |    3 |
| Brown |    20 |    3 |
| Jones |    15 |    5 |
| Smith |    10 |    6 |
+-------+-------+------+

More Related Contents:

  1. In SQL Server how to get the count of child if both parent and child are available [closed]
  2. ANYONE CAN TELL ME THAT WHY THIS CODE IS GIVING ME AN ERROR OF TABLE OT CLUSTER KEY WORD IS MISSING? [closed]
  3. How do I escape a reserved word in Oracle?
  4. How to put more than 1000 values into an Oracle IN clause [duplicate]
  5. ORA-30926: unable to get a stable set of rows in the source tables
  6. ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
  7. Stored Procedure with optional “WHERE” parameters
  8. Fetch Oracle table type from stored procedure using JDBC
  9. “Safe” TO_NUMBER()
  10. When or Why to use a “SET DEFINE OFF” in Oracle Database
  11. error: ORA-65096: invalid common user or role name in oracle
  12. Oracle insert if not exists statement
  13. Error : ORA-01704: string literal too long
  14. Oracle client ORA-12541: TNS:no listener [closed]
  15. Create an Excel Spreadsheet from a Oracle Database
  16. How do I turn off Oracle password expiration?
  17. ORACLE IIF Statement
  18. Table-Valued Functions in ORACLE 11g ? ( parameterized views )
  19. How to increase performance for bulk INSERTs to ODBC linked tables in Access?
  20. Oracle Age calculation from Date of birth and Today
  21. ORA-12705: Cannot access NLS data files or invalid environment
  22. jboss 7 oracle datasource configuration
  23. How to see refcursor result/output in Oracle SQL Developer? [duplicate]
  24. How do I list all the columns in a table?
  25. How to convert “1985-02-07T00:00:00.000Z” (ISO8601) to a date value in Oracle?
  26. How to generate a version 4 (random) UUID on Oracle?
  27. Oracle SQL Developer: Show REFCURSOR Results in Grid?
  28. Find out the history of SQL queries
  29. How to test an Oracle Stored Procedure with RefCursor return type?
  30. Getting Error – ORA-01858: a non-numeric character was found where a numeric was expected

Leave a Comment