Changes between Version 28 and Version 29 of Onyx to CiviCRM

Timestamp:

01/07/14 09:28:09 (10 years ago)

Author:

Richard Bramley

Comment:

--

Onyx to CiviCRM

@@ -125 +125 @@
     GROUP BY interview_id
     ) mra ON mra.interview_id = i.id
+WHERE
+    /*
+        LIMIT the query to select only the first x records
+        ordered by most_recent_action, but include all the
+        ties for the last place.
+
+        This allows us to filter the query using
+        most_recent_action > last_most_recent_action
+        as opposed to most_recent_action >= last_most_recent_action,
+        which would start to fail if there were more than x
+        records with a most_recent_action of last_most_recent_action.
+    */
+    mra.most_recent_action IN (
+        SELECT DISTINCT most_recent_action
+        FROM (
+            SELECT
+                  interview_id
+                , MAX(date_time) most_recent_action
+            FROM action
+            GROUP BY interview_id
+            WHERE date_time > '01-jan-2000' /* last_most_recent_action */
+            ORDER BY MAX(date_time) ASC
+            LIMIT 10
+        ) adf
+    )
 ORDER BY mra.most_recent_action ASC
 ;