hi,
so i am having a related problem, i currently use your suggestions
like so...
test = DB[:New_Stats].filter(:src_file_id => all_src_files).where
(:Experiment_ID => 2).group_and_count
(:src_file_id, :Channel, :Table_id).having{count > 1}
which gives me a resulting dataset containing,
{:Channel, :count, :Table_id, :src_file_id}
what i would like is to be able to get the Stat_ID (the unique primary
key of :New_Stats)
essentially some thing like this (when expressed in SQL )
SELECT `Stat_ID` , `src_file_id` , `Channel` , `Table_id` , count( * )
AS `count`
FROM `New_Stats` WHERE ( ( `src_file_id` IN ( SELECT `id` FROM
`src_files_table` WHERE ( `src_camera` =2 ) ) ) AND ( `Experiment_ID`
=2 ) ) GROUP BY `src_file_id` , `Channel` , `Table_id` HAVING
( `count` >1 )
but when i modify my expression above to say something like
test = DB[:New_Stats].filter(:src_file_id => all_src_files).where
(:Experiment_ID => 2).select
(:src_file_id, :Channel, :Table_id, :Stat_ID).group_and_count
(:src_file_id, :Channel, :Table_id).having{count > 1}
the returned data still doesnt include the Stat_ID column, where do i
put the select statement to pull out this unique column, so its not
included in the group_and_count function, but is still returned in the
dataset ?
i can of course just use the SQL i have worked out, but it would be
nice to keep things in the sequel style which is really great!
cheers
Joe
On Jan 14, 1:42 pm, Jeremy Evans <[email protected]> wrote:
> On Jan 14, 10:47 am, Joe Lovick <[email protected]> wrote:
>
> > Hi,
>
> > I have a optiminsation problem, I have a beutiful but large db (22Gb, yes
> > you read that right) in Mysql, and i made a mistake in populating part of
> > it, so i thought that i would write a quick script with sequel to weed out
> > the data that shouldnt be there, unfortunately it runs too slowly to be of
> > use...
>
> I'd use the aggregate functions of the database to do the work for
> you:
>
> DB[:New_Stats].
> where(:Experiment_ID => 2).
> group_and_count(:src_file_id, :Stat_ID, :Channel, :Table_id).
> having{count > 1}
>
> Assuming I understand your situation, you should just have to process
> the results of that dataset.
>
> Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
