8-)

I like this SQL trap because I believe that it is natural to want to sort descending by an absolute aggregated value.

The real issue is that the alias of the result column being the same as the name of an input column causes some SQL engines to deliver incorrect results without alerting the programmer to the hidden problem.

Many SQL engines attempt to eliminate the sort phase by assembling the data in the correct order whilst examining / aggregating input rows. In most engines, a GROUP BY will automatically order a result set, but when present, the ORDER BY will take precedence. When the ORDER BY contains a function - e.g ABS() the function is called with the current input row value - not the aggregated resultset value which has not yet been finalised.

Often, the results are only SOMETIMES incorrect - the nastiest form of bug. I tracked down an instance of this rare beetle, that was only seen on a handful of occasions in two years of processing.

When I raised it with ****, the initial response was 'Why would you want to anyway?', followed by redirection to details of the SQL spec which indeed does not mention this facility.

The following SQL at least complains if the SQL engine doesn't support sorting on an absolute aggregated value: 

  select 
    name, 
    sum(value) as sumval 
  from mytable
  group by name
  order by abs(sumval) desc

[download]

I prefer named resultset columns rather than 

  order by abs(2) desc

[download]

I haven't worked with Postgresql to know if it does the right thing - which 'value' column was summed, absed and sorted during the query? Changing the alias would show us if it is really doing the Right Thing - if so, hoorah! A second DB that caters to common sense!

FWIW the DWIM DB engine that works in this case is probably long defunct - I haven't seen it for years - it was called FoxPro, and I think it was captured by the Big Bad Wolf.

In reply to Re: Re:x3 Array of Hash interface to an Array of Array? (grouping in SQL) by jaa
in thread Array of Hash interface to an Array of Array? by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.