ROW_NUMBER() in MySQ

I want the row with the single highest col3 for each (col1, col2) pair.

That’s a groupwise maximum, one of the most commonly-asked SQL questions (since it seems like it should be easy, but actually it kind of isn’t).

I often plump for a null-self-join:

SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

“Get the rows in the table for which no other row with matching col1,col2 has a higher col3.” (You will notice this and most other groupwise-maximum solutions will return multiple rows if more than one row has the same col1,col2,col3. If that’s a problem you may need some post-processing.)

Related Posts:

  1. MySQL – Get row number on select
  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. SQL select only rows with max value on a column
  38. How do you force mysql LIKE to be case sensitive?
  39. MySQL – UPDATE multiple rows with different values in one query
  40. How to create a MySQL hierarchical recursive query?
  41. ERROR 1148: The used command is not allowed with this MySQL version
  42. Insert into a MySQL table or update if exists
  43. How to round down to nearest integer in MySQL?
  44. MySQL starts with searching issue
  45. Query to convert from datetime to date mysql
  46. Is it possible to GROUP BY multiple columns using MySQL?
  47. MySQL error: key specification without a key length
  48. Equivalent of Oracle’s RowID in SQL Server
  49. Best way to test if a row exists in a MySQL table
  50. Check if MySQL table exists without using “select from” syntax?
  51. MySQL equivalent of DECODE function in Oracle
  52. mysql update column with value from another table
  53. The used SELECT statements have a different number of columns
  54. How to delete from multiple tables in MySQL?
  55. MySQL: Can’t create table (errno: 150)
  56. How to copy a row and insert in same table with a autoincrement field in MySQL?
  57. Join vs. sub-query
  58. Alternative to except in MySQL
  59. Equivalent of explode() to work with strings in MySQL
  60. MySQL OPTIMIZE all tables?
  61. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  62. MySQL error: Unknown column in ‘where clause’
  63. How to truncate the text returned for a column in a MySQL query
  64. Using union and order by clause in mysql
  65. MySQL LIKE IN()?
  66. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  67. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  68. How can I do an UPDATE statement with JOIN in SQL Server?
  69. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  70. Drop a temporary table if it exists
  71. ORA-00918: column ambiguously defined in SELECT *
  72. Best way to do nested case statement logic in SQL Server
  73. Get day of week in SQL Server 2005/2008
  74. mysql Foreign key constraint is incorrectly formed error
  75. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  76. Postgresql tables exists, but getting “relation does not exist” when querying
  77. SQL Parentheses use in an OR clause
  78. What is the difference between LATERAL JOIN and a subquery in PostgreSQL?
  79. Repair all tables in one go
  80. Is it possible to specify condition in Count()?
  81. SQL Server: Make all UPPER case to Proper Case/Title Case
  82. How do I query for all dates greater than a certain date in SQL Server?
  83. Temporary table in SQL server causing ‘ There is already an object named’ error
  84. Append table to an existing one: SQL Server
  85. How to order by column A and then by column B?
  86. SQL Server – An expression of non-boolean type specified in a context where a condition is expected, near ‘RETURN’
  87. register_post_status – show_in_admin_all_list & show_in_admin_status_list does not affect query
  88. How to get the list of WooCommerce product image of a certain category from database?
  89. prepare() not working
  90. How do I move a WordPress site to another server?
  91. How do I get the posts within a certain year/date(with sql query)?
  92. how to insert missing tags into the posts through mySQL?
  93. SQL query to set posts in bulk based on the post content
  94. DB_HOST – is LOCALHOST speedy than domain name? [closed]
  95. JOIN and SUM different statement results (Mailster Database)
  96. Performing CRUD operations on front end in wordpress
  97. How to show result of sql query in a page?
  98. blank page after update my theme
  99. SQL command to export post_content from wp_posts using phpMyAdmin
  100. Adding profile data to database

Leave a Comment

tech