Subtracting minimum value from all values in a column

Question

Is there a another way to subtract the smallest value from all the values of a column, effectively offset the values?

The only way I have found becomes horribly complicated for more complex queries.

CREATE TABLE offsettest(value NUMBER);
INSERT INTO offsettest VALUES(100);
INSERT INTO offsettest VALUES(200);
INSERT INTO offsettest VALUES(300);
INSERT INTO offsettest VALUES(400);

SELECT value - (SELECT MIN(value) FROM offsettest) FROM offsettest;

DROP TABLE offsettest;

I'd like to limit it to a single query (no stored procedures, variables, etc) if possible and standard SQL is preferred (although I am using Oracle).

Answer 1

@gregory in comments The example was only to highlight the problem without the unnecessary fluff. The production tables are slightly more complex and the provided solution would introduce (in my eyes) unnecessary code duplication.

There may not be a simpler solution (as Daniel points out), which will mean I will have to revise the technologies I'm using (there's a mix of in-house and commercial software, hence the strange limitations).

Answer 2

I believe this works as of ANSI 1999.

SELECT value - MIN(value) OVER() FROM offsettest;

Answer 3

lins314159's solution works

If a someone mod privileges could mark this as correct (and clear up all these extraneous answers since anon can't post comments), that would be awesome.

Answer 4

It would have helped you see your actual query, though, since depending on whether you need to manipulate more than one column this way, and the various minimums come from different rows, there may be more efficient ways to do it. If the OVER() works for you, then fine.