1

I need to make a query like this:

SELECT (t.a-t.b) AS 'difference'
FROM t
ORDER BY abs(t.a-t.b)

Is there a way not to duplicate code (t.a-t.b) ? Thank you for your answers

awfun
  • 2,316
  • 4
  • 31
  • 52

1 Answers1

2

You can wrap the SQL statement and then perform the ORDER BY if you're performing an absolute value on it.

SELECT * FROM
(
   SELECT (t.a-t.b) AS "difference"
   FROM t
) a
ORDER BY abs(a.difference)

UPDATE: I used SQL Server the 1st time, but depending on your environment (Oracle, MySQL), you may need to include double quotes around the column alias, so:

SELECT * FROM
(
   SELECT (t.a-t.b) AS "difference"
   FROM t
) a
ORDER BY abs("a.difference")
larsenmc
  • 81
  • 5
  • If you want to read more about it this is a very similar post. http://stackoverflow.com/questions/11182339/reference-alias-calculated-in-select-in-where-clause – Holmes IV Jul 21 '15 at 16:33
  • Please see my comment for #1 answer: it says : "invalid column name: difference" – awfun Jul 21 '15 at 16:36
  • Works for me, but try prefacing the column with 'a', so 'abs(a.difference)'. – larsenmc Jul 21 '15 at 16:48
  • Added an update about including double quotes around column alias. Let me know if you're still having trouble. – larsenmc Jul 21 '15 at 17:06
  • That's all right now, thank you. Quotes not needed in parameter of abs(). Maybe I was doing something wrong before – awfun Jul 21 '15 at 17:16