I want to select all rows from one table and insert them into another with only the max and min values on all rows. Cant figure out how to write this without a group by clause. Its a big table so (update set=? from (select max...)) is to slow
table1:
id,values
1,2
2,4
3,1
table2:
id,max,min
1,4,1
2,4,1
3,4,1
Here another try using window functions (SQLFiddle: http://sqlfiddle.com/#!15/2978e) This is the select to get your data.
select
id,
min(values) over () as min,
max(values) over () as max
from Table1
To insert these values one has to execute this sql
insert into table2 (id, min, max)
select
id,
min(values) over () as min,
max(values) over () as max
from Table1
or to create table2 from the sql
create table table2 as
select
id,
min(values) over () as min,
max(values) over () as max
from Table1
This will do it:
SELECT n1.id,
n2.mymax,
n2.mymin
FROM
(SELECT MIN(values) AS mymin,
MAX(values) AS mymax
FROM dbTable) n2,
dbTable n1;
SQL Fiddle here:
