Peter Brawley Wrote:
-------------------------------------------------------
> > where dt.text = act_gen_raw.DateTime
>
> Datatype matters. MySQL date funcs aren't designed
> for text, so dt.txt should be datetime or
> timestamp and its name should reflect that.
Ok have converted the data now.
> You're thinking procedurally. SQL is about
>
sets. Row-by-row processing in SQL is much
> more complex to code & debug, and orders of
> magnitude slower. Do it only as a last resort.
Yes I do understand that, I am just still new to sql and was trying to layout what kind of dataset I would like to achieve. Sorry if that caused for confusion.
> to make the dataset usable, write a query that
> finds the act_gen_raw rows matching plant_info
> rows in the dataset, compose a WHERE clause
> specifying those values, hand it to mysqldump.
> Ditto for act_gen_raw to dt.
Why do I need to hand it to mysqldump? Can't I create directly a table? Sorry I am still new to this.
I think with the following code I am finally able to select the data I want:
select dt.`d`, dt.t, `act_gen_raw`.ActualGenerationOutput, plant_information.PowerSystemResourceName as info, `act_gen_raw`.PowerSystemResourceName as raw from `act_gen_raw`, dt, plant_information
Where (`power_plants_raw`.`act_gen_raw`.`datetime_variable` in(select dt.datetime_variable from dt)) AND (plant_information.PowerSystemResourceName = `act_gen_raw`.PowerSystemResourceName)
group by `act_gen_raw`.PowerSystemResourceName
order by dt.`d`, dt.`t`;
[\code]
What I still do not get is how I can generate a new column for each "group" of my data respectively for each value of "PowerSystemResourceName". And also how I can make sure this columns name is exactly the value of this "PowerSystemResourceName"?
> Re your spec, row1...last_row on what ordering?
Again I was just trying to layout what kind of table I would like to achieve. Here the order does not matter.