pheromix pheromix - 5 months ago 10
SQL Question

Where to place SQL_CALC_FOUND_ROWS against the DISTINCT keyword?

I cannot get the result returned by

SQL_CALC_FOUND_ROWS
so I post the thread : where is the correct position to place SQL_CALC_FOUND_ROWS against the DISTINCT keyword , example
SELECT DISTINCT myColumn FROM myTable WHERE someConditions;

Answer

The SELECT syntax is this:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [MAX_STATEMENT_TIME = N]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]

[...]

In practice, order does not seem to matter as long as you respect the overall syntax (e.g., don't put WHERE before FROM):

mysql> SELECT DISTINCT SQL_CALC_FOUND_ROWS COUNT(*) FROM emails;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT SQL_CALC_FOUND_ROWS DISTINCT COUNT(*) FROM emails;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
Comments