Changeset 18609

Timestamp:

Jul 17, 2008, 4:12:41 PM (18 years ago)

Author:

Paul Price

Message:

That ORDER BY which I claimed was so important wasn't really. I think I was in shaky ground so far as standard SQL is concerned (undefined behaviour). By explicitly taking the MAX, I don't need to ORDER BY and then hope that it chooses the right one. Besides, MAX is faster than ORDER BY.

Location:

trunk/ippTools/share

Files:

• stacktool_definebyquery_part1.sql (modified) (1 diff)
• stacktool_definebyquery_part2.sql (modified) (1 diff)
• stacktool_definebyquery_test.sql (modified) (4 diffs)

trunk/ippTools/share/stacktool_definebyquery_part1.sql

@@ -4 +4 @@
 
 SELECT
-    *
+    skycell_id,
+    tess_id,
+    filter,
+    num_warp,
+    MAX(num_stack) AS num_stack
 FROM ((
     -- Number of stack-ready warps as a function of skycell and filter

trunk/ippTools/share/stacktool_definebyquery_part2.sql

@@ -20 +20 @@
     GROUP BY
         stack_id
-    -- This ORDER BY is important: it sets the final num_stack that
-    -- comes out of the top-level GROUP BY.  We want it to be the
-    -- biggest number for a skycell/filter combination.
-    ORDER BY
-        num_stack DESC
         ) AS stackSizes
     -- JOINing the warpsToStack and stackSizes tables

trunk/ippTools/share/stacktool_definebyquery_test.sql

@@ -11 +11 @@
 -- stacktool_definebyquery_part1.sql
 SELECT
-    *
+    skycell_id,
+    tess_id,
+    filter,
+    num_warp,
+    MAX(num_stack) AS num_stack
 FROM ((
     -- Number of stack-ready warps as a function of skycell and filter
@@ -30 +34 @@
     AND warpSkyfile.fault = 0
     -- Any additional selection on warps/exposures goes here
-
 -- stacktool_definebyquery_part2.sql
     GROUP BY
@@ -42 +45 @@
         tess_id,
         filter,
-        stack_id,
         COUNT(stackInputSkyfile.warp_id) as num_stack -- number of warps in a stack
     FROM stackRun
@@ -48 +50 @@
     GROUP BY
         stack_id
-    -- This ORDER BY is important: it sets the final num_stack that
-    -- comes out of the top-level GROUP BY.  We want it to be the
-    -- biggest number for a skycell/filter combination.
-    ORDER BY
-        num_stack DESC
         ) AS stackSizes
     -- JOINing the warpsToStack and stackSizes tables