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.