On 9/6/07, Ow Mun Heng <Ow.Mun.Heng@xxxxxxx> wrote: > Table is like > > create table foo ( > number int, > subset int, > value int > ) > > select * from foo; > number | subset | value > 1 1 1 > 1 2 2 > 1 3 10 > 1 4 3 > > current query is like > > select number, > avg(case when subset = 1 then value else null end) as v1, > avg(case when subset = 2 then value else null end) as v2, > avg(case when subset = 3 then value else null end) as v3, > avg(case when subset = 4 then value else null end) as v4 > from foo > group by number arrays are interesting and have some useful problems. however, we must first discuss the problems...first and foremost if you need to read any particular item off the array you must read the entire array from disk and you must right all items back to disk for writes. also, they cause some problems with constraints and other issues that come up with de-normalization tactics. however, If a particular data is expressed actually as an array of items (the polygon type comes to mind), then why not? let'l that said, let's look at a better way to express this query. what jumps out at me right away is: select number, subset, avg(value) from foo group by subset; does this give you the answer that you need? If not we can proceed and look at why arrays may or may not be appropriate (i suspect I am not seeing the whole picture here). merlin ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
