Re: how to "group" several records with same timestamp into one line?

Hi Ho!

--- On Thu, 11/13/08, Brent Wood <b.wood@niwa.co.nz> wrote:

> You need to use a self relation, not a group by, as no data
> are being aggregated into a new single value, which is what
> the group by achieves.
>
> This joins a table to itself, so that columns in it can be
> replicated. The key is that the where clause in each case
> needs to just select one channel, so it acts like a similar
> table but contains different rows.
>
> Because you used a char() instead of varchar() for channel,
> you may find your string 'channel1' has spaces in it
> to pad it to the specified length, in which case the where
> clauses below can use "like '%channel1%'"
> instead of "= 'channel1'"
> or you can strip the spaces before the comparison, eg:
> where "trim(a.channel)='channel1'". I hope
> this makes sense.
>
> eg: select a.data,
>                 a.unit,
>                 b.data,
>                 b.unit,
>                 c.data,
>                 c.unit,
>                 d.data,
>                 d.unit,
>                 a.create_on
>    from record data a,
>           record-data b,
>           record_data c,
>           record_data d
>    where a.channel='channel1'
>        and b.channel='channel2'
>        and c.channel='channel3'
>        and d.channel=channel4
>        and b.create_on = a.create_on
>        and c.create_on = a.create_on
>        and d.create_on = a.create on;
>
> Thus table a comprises all records from channel1, etc...
> and they are joined on a common timestamp.
>
> NOTE: if any of the channels are null for any given
> timestamp, you will get no record for that timestamp using
> this syntax, even if other channels had data, because the
> query uses an inner join. If this is a problem then
> you'll need to reword the query as an outer join.

Isn't that something like this is better handled at the application level instead of the DB level?

IOW, isn't that the cost of doing the query above far more expensive than doing a little coding at the application
level?

May I know your opinion?

Thanks.

> HTH,
>
>    Brent Wood
>
>
> Brent Wood
> DBA/GIS consultant
> NIWA, Wellington
> New Zealand

Best regards,
Eus (FSF member #4445)

In this digital era, where computing technology is pervasive,
your freedom depends on the software controlling those computing devices.

Join free software movement today!
It is free as in freedom, not as in free beer!

Join: http://www.fsf.org/jf?referrer=4445