title: Oracle MIN as analytic function - odd behavior with ORDER BY?
body: <p>This particular case was distilled from an example where the programmer assumed that for two shipments into a tank car, line #1 would be loaded first.  I corrected this to allow for the loading to be performed in any order - however, I discovered that <code>MIN() OVER (PARTITION BY)</code> allows an <code>ORDER BY</code> in Oracle (this is not allowed in SQL Server), and additionally, it alters the behavior of the function, causing the <code>ORDER BY</code> to apparently be added to the <code>PARTITION BY</code>.</p>
  
  <pre><code>WITH data AS (
  SELECT 1 AS SHIPMENT_ID, 1 AS LINE_NUMBER, 2 AS TARE, 3 AS GROSS FROM DUAL
  UNION ALL
  SELECT 1 AS SHIPMENT_ID, 2 AS LINE_NUMBER, 1 AS TARE, 2 AS GROSS FROM DUAL
  )
  SELECT MIN(tare) OVER (PARTITION BY shipment_id) first_tare
  ,MAX(gross) OVER (PARTITION BY shipment_id) last_gross
  ,FIRST_VALUE(tare) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER) first_tare_incorrect
  ,FIRST_VALUE(gross) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER DESC) last_gross_incorrect
  ,MIN(tare) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER) first_tare_incorrect_still
  ,MAX(gross) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER DESC) last_gross_incorrect_still
  ,MIN(tare) OVER (PARTITION BY shipment_id, LINE_NUMBER) first_tare_incorrect_still2
  ,MAX(gross) OVER (PARTITION BY shipment_id, LINE_NUMBER) last_gross_incorrect_still2
   FROM data
  </code></pre>
  
  <p>A SQL Server example (with non-applicable code commented out):</p>
  
  <pre><code>WITH data AS (
  SELECT 1 AS SHIPMENT_ID, 1 AS LINE_NUMBER, 2 AS TARE, 3 AS GROSS -- FROM DUAL
  UNION ALL
  SELECT 1 AS SHIPMENT_ID, 2 AS LINE_NUMBER, 1 AS TARE, 2 AS GROSS -- FROM DUAL
  )
  SELECT MIN(tare) OVER (PARTITION BY shipment_id) first_tare
  ,MAX(gross) OVER (PARTITION BY shipment_id) last_gross
  -- ,FIRST_VALUE(tare) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER) first_tare_incorrect
  -- ,FIRST_VALUE(gross) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER DESC) last_gross_incorrect
  -- ,MIN(tare) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER) first_tare_incorrect_still
  -- ,MAX(gross) OVER (PARTITION BY shipment_id ORDER BY LINE_NUMBER DESC) last_gross_incorrect_still
  ,MIN(tare) OVER (PARTITION BY shipment_id, LINE_NUMBER) first_tare_incorrect_still2
  ,MAX(gross) OVER (PARTITION BY shipment_id, LINE_NUMBER) last_gross_incorrect_still2
   FROM data
  </code></pre>
  
  <p>So question:  What is Oracle doing and why and is it right?</p>